[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] Where do we post to share code? CTE: Long to Wide format
From:       "joe.fisher () tanguaylab ! com" <joe ! fisher () tanguaylab ! com>
Date:       2014-12-31 0:23:51
Message-ID: 54A34217.7000406 () tanguaylab ! com
[Download RAW message or body]

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 ('000005713', 2, 46.8, 1);
INSERT INTO long_to_wide VALUES ('000005713', 2, 33.9, 2);
INSERT INTO long_to_wide VALUES ('000005713', 2, 27.9, 3);
INSERT INTO long_to_wide VALUES ('000005713', 2, 25.6, 4);
INSERT INTO long_to_wide VALUES ('000005713', 4, 19.0, 1);
INSERT INTO long_to_wide VALUES ('000005713', 4, 44.3, 2);
INSERT INTO long_to_wide VALUES ('000005713', 4, 48.8, 3);
INSERT INTO long_to_wide VALUES ('000005713', 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
[prev in list] [next in list] [prev in thread] [next in thread]