I think the following works: SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic FROM (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Systolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) s JOIN (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Diastolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) d ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r ORDER BY s.ID, s.Date;
You will need a fairly recent version of SQLite, older versions don't have window functions. And I have assumed that there is a Diastolic value for every Systolic value and vice versa. Jen On Mon, Feb 10, 2020 at 12:24:33AM +0000, Bart Smissaert wrote: > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > ---------------------------------------------------------------- > 1308 15/Mar/2013 Systolic 127 701559 > 1308 15/Mar/2013 Diastolic 81 701568 > 1308 27/Jun/2013 Systolic 132 701562 > 1308 27/Jun/2013 Systolic 141 701563 > 1308 27/Jun/2013 Systolic 143 701564 > 1308 27/Jun/2013 Diastolic 82 701571 > 1308 27/Jun/2013 Diastolic 85 701572 > 1308 27/Jun/2013 Diastolic 94 701573 > 278975701 08/Mar/2018 Systolic 136 1583551 > 278975701 08/Mar/2018 Diastolic 99 1583591 > 278975701 04/Apr/2018 Systolic 119 1583552 > 278975701 04/Apr/2018 Systolic 124 1583553 > 278975701 04/Apr/2018 Systolic 130 1583554 > 278975701 04/Apr/2018 Diastolic 74 1583592 > 278975701 04/Apr/2018 Diastolic 75 1583593 > 278975701 04/Apr/2018 Diastolic 85 1583594 > > These are systolic and diastolic blood pressures for 2 people with the ID's > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > Systolic and diastolic values are a pair and should be grouped in one row. > This is no problem if there is only one pair for one date, but sometimes > there multiple pairs per date. > The pairing should be based on the rowed if there are multiple pairs by > date, so for ID 1308 > I should get: > > 127/81 > 132/82 > 141/85 > 143/94 > > What should be the SQL to group like this? > > RBS > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
