SQL - How to pivot and combine data across rows

Question

Am tyring to model on a dataset with individual person's vitals for a wellness app, imagine like Fitbit. However, these vitals, for a given person are recorded and reported as individual rows. E.g.

TableName: PersonalVitals

PersonID | RecordedTime | VitalType | VitalValue 
   1     |    17:10     |   HR      |    72bpm       <- Heart rate
   1     |    17:10     |   RR      |    19insp/min  <- Respiratory rate
   2     |    15:35     |   RR      |    11insp/min  <- Respiratory rate
   1     |    17:15     |   HR      |    76bpm       <- Heart rate

Now, on extraction, I need to pivot this data, so that all the vitals that were taken during the same time, are reported together, something like below

PersonID | RecordedTime |   HR   |   RR        |  SpO2   |  BP(Blood Pressure)
   1     |    17:10     |  72bpm | 19insp/min  |  null   |    null
   1     |    17:15     |  76bpm |    null     |  null   |    null  
   2     |    15:35     |  null  | 11insp/min  |  null   |    null

How can I achive the above in SQL (sql-server or postgres)?

Thanks

Is the list of vital types fixed? (There is no SpO2 in your sample data) — user330315
– user330315, Commented Aug 2, 2019 at 12:24
This list of vitals is fixed, albiet a somewhat big list, so I have just curated the above example to show few of the vitals. — sppc42
– sppc42, Commented Aug 2, 2019 at 12:35

user330315 · Accepted Answer · 2019-08-02 12:51:37Z

1

With Postgres I would aggregate all vitals into a single JSONB value and then extract them as columns:

select person_id, 
       recorded_time, 
       vitals ->> 'HR' as "HR", 
       vitals ->> 'RR' as "RR", 
       vitals ->> 'SpO2' as "SpO2", 
       vitals ->> 'BP' as "Blood Pressure"
from (
  select person_id, recorded_time, 
         jsonb_object_agg(vital_type, vital_value) as vitals
  from person_vitals 
  group by person_id, recorded_time
) t
order by person_id, recorded_time;

Online example: https://rextester.com/ONNU50709

As with all pivot solutions, you will need to change the query if you add new vital types. With the above solutions, it's enough to do that in the outer query. The inner query does not need to be changed.

If the code processing that result can deal with a JSON value, I would just return the result of the inner query directly, rather than turning that into individual columns.

answered Aug 2, 2019 at 12:51

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

sppc42 Over a year ago

This solution is great, but requires upfront knowledge of vitals so that they can be added in 'select' list of outer query. If, however, the list of vitals is not known, then how can the above query be amended to list all the vitals that are returned from the inner query as individual columns?

user330315 Over a year ago

@sppc42: if the list of vitals is not know, you can't amend that query to deal with that. One of the restrictions of SQL is, that the number of columns (and their data types) has to be know to the database before actually executing the query. The only way to get that in a "dynamic" way is to return the JSON value from the inner query to your application and deal with the "pivot" part in the front end (which is almost always the better choice anyway)

Sreenu131 · Accepted Answer · 2019-08-02 12:29:20Z

1

Using Pivot

SELECT PersonID , RecordedTime,[HR],[RR] ,SpO2,[BP(Blood Pressure)]
FROM
(
SELECT * ,NULL AS  SpO2,NULL AS [BP(Blood Pressure)]
FROM #PersonalVitals
)
As Src
PIVOT
(
MAX(VitalValue) FOR VitalType IN ([HR],[RR]))AS Pvt
ORDER BY PersonID

answered Aug 2, 2019 at 12:29

Sreenu131

2,5361 gold badge10 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL - How to pivot and combine data across rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related