Re: [sqlite] Where do we post to share code? CTE: Long to Wide format

[Klaas V]

Happy New Year, Joe and other SML-members,
You answered your own question in the title of your post perfectly.
If you want to post the results of .fullschema here as well you provide us with 
almost every detail of the way you coded your database.
Perhaps you mean other code in what language and under which operating system 
except SQL; please specify what kind of coding you'd like to share with us. 
Unfortunately the managers of this list don't allow us to attach files so only 
source code.  
Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Where do we post to share code? CTE: Long to Wide format

[joe.fis...@tanguaylab.com]

I love Common Table Expressions (CTE) in SQLite.
Databases are long format data but other programs (e.g., IBM SPSS 
Statistics) are sometimes wide format.


Joe Fisher
Oregon State University


Here's some useful code:

CREATE TABLE [long_to_wide] (
  [barcode_id] TEXT(16) NOT NULL,
  [well_no] INTEGER NOT NULL,
  [my_value] REAL NOT NULL,
  [my_time] INTEGER NOT NULL);

INSERT INTO long_to_wide VALUES ('05713', 2, 46.8, 1);
INSERT INTO long_to_wide VALUES ('05713', 2, 33.9, 2);
INSERT INTO long_to_wide VALUES ('05713', 2, 27.9, 3);
INSERT INTO long_to_wide VALUES ('05713', 2, 25.6, 4);
INSERT INTO long_to_wide VALUES ('05713', 4, 19.0, 1);
INSERT INTO long_to_wide VALUES ('05713', 4, 44.3, 2);
INSERT INTO long_to_wide VALUES ('05713', 4, 48.8, 3);
INSERT INTO long_to_wide VALUES ('05713', 4, 47.8, 4);

;with myCTE1 as
(
select (barcode_id || '-' || well_no) as unique_id, my_value as Time1
from long_to_wide
where my_time = 1
),
myCTE2 as
(
select (barcode_id || '-' || well_no) as unique_id, my_value as Time2
from long_to_wide
where my_time = 2
),
myCTE3 as
(
select (barcode_id || '-' || well_no) as unique_id, my_value as Time3
from long_to_wide
where my_time = 3
),
myCTE4 as
(
select (barcode_id || '-' || well_no) as unique_id, my_value as Time4
from long_to_wide
where my_time = 4
)
select distinct myCTE1.unique_id as unique_id, myCTE1.Time1 as Time1, 
myCTE2.Time2 as Time2, myCTE3.Time3 as Time3, myCTE4.Time4 as Time4

from myCTE1
inner join myCTE2 on myCTE1.unique_id = myCTE2.unique_id
inner join myCTE3 on myCTE1.unique_id = myCTE3.unique_id
inner join myCTE4 on myCTE1.unique_id = myCTE4.unique_id
order by myCTE1.unique_id
;

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users