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 ;
should be select id, entry_date, max(case when term == 'Systolic' then reading end) || '/' || max(case when term == 'Diastolic' then reading end) as Pressure from the_table group by 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 Keith Medcalf >Sent: Sunday, 9 February, 2020 19:17 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] How to group this? > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users