select id, entry_date, max(case when term == 'Systolic' then reading end) as Systolic, max(case when term == 'Diastolic' then reading end) as Diastolic from the_table group by id, entry_date ;
If you want to make sure you have both terms for a given id/entry_date then you need to make it a subquery so you can check for null. select id, entry_date, systolic || '/' || diastolic as pressure from ( select id, entry_date, max(case when term == 'Systolic' then reading end) as Systolic, max(case when term == 'Diastolic' then reading end) as Diastolic from the_table group by id, entry_date ) where systolic is not null and diastolic is not null ; Note that (id, entry_date, term) needs to be unique ... or you will just get the combination of the max(systolic)/max(diastolic) for the results for a given id/entry_date. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Bart Smissaert >Sent: Sunday, 9 February, 2020 17:25 >To: General Discussion of SQLite Database <sqlite- >us...@mailinglists.sqlite.org> >Subject: [sqlite] How to group this? > >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