27 mrt 2017, petern:
In general I've been thinking about materializing data dependent
temporary
tables and even using them in CTE's. The tremendous expressive
economy of
TCL and somewhat built-in support within SQLite got me thinking.
Consider
the problem of pivot table function for creating temp.crosstab_*
summary
tables...
I left out the example command line script from you reply. This
clarifies your somewahat abstract requirement. Now I also see why
earlier on you wished eval() to return query result. But, as eval()
does ddl too I just make it define a temp view. That can be selected
from afterwards. See below. Thanks for the TCL example that helps me
learn. Best regards, E. Pasma, sponsor of https://facebook.com/RadioParadijs
.version
SQLite 3.18.0 2017-03-06 20:44:13
ec529bf11b16c801ea438e57d208ff7e4cedf1f9
select load_extension('eval');
drop table if exists sales;
create table sales(year integer,fruit text,tons integer);
insert into sales
values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7),
(2017,'peaches',6),(2016,'mangos',12);
--select * from sales;
select ddl, eval(ddl)
from (
select 'create temp view vttt as select year, '
|| group_concat('sum(case when fruit='''
|| fruit
|| ''' then tons end) as '
|| fruit)
|| ' from sales group by year'
as ddl
from ( select fruit from sales group by fruit)
)
;
create temp view vttt as select year, sum(case when fruit='apples'
then tons end) as apples,sum(case when fruit='mangos' then tons end)
as mangos,sum(case when fruit='peaches' then tons end) as peaches from
sales group by year|
.header on
select * from vttt;
year|apples|mangos|peaches
2016||12|2
2017|7|3|6
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
