I'm sorry if this fells like code golf, but I trying to avoid getting data out of the database, do processing in application code for something that would require slooow loops over a data set where there are indices that would help in the grouping.
Is there any way at all to get from a data set like this:
sqlite> create table tst (id INTEGER, label TEXT, variable TEXT, value FLOAT);
sqlite> insert into tst values (1,"label1","variable 1",1.2);
sqlite> insert into tst values (1,"label2","variable 2",2.2);
sqlite> insert into tst values (1,"label3","variable 3",3.2);
sqlite> select * from tst;
id label variable value
---------- ---------- ---------- ----------
1 label1 variable 1 1.2
1 label2 variable 2 2.2
1 label3 variable 3 3.2
to be returned like this (with the names of the output columns being constructed from the content of the variable and label column content):
sqlite> <magic query here>
id label1_variable1 label2_variable2 label3_variable3
---------- ---------- ---------- ----------
1 1.2 2.2 3.2
that is, in wide format rather than long format?
Please advice, or help me just by proving that is cannot be done.
You can achieve this using a pivot query:
SELECT id,
SUM(CASE WHEN label = 'label1' THEN value ELSE 0 END) AS 'label1_variable1',
SUM(CASE WHEN label = 'label2' THEN value ELSE 0 END) AS 'label2_variable2',
SUM(CASE WHEN label = 'label3' THEN value ELSE 0 END) AS 'label3_variable3'
FROM tst
GROUP BY id
You cannot perform a pivot query in SQLite with an unknown set of columns because doing so would require dynamic SQL. As user @CL. mentioned in his comment below, one way to simulate dynamic SQL would be to first do a SELECT DISTINCT label FROM tst to obtain the set of all columns, followed by a pivot query resembling what I gave above. You would have to construct the pivot query programatically from your app layer.