Postgresql Group By and create columns for different values-CodePudding

I'm using Postgresql and have this table:

device_id	date	variable_name	pulses
1	2021-03-29	height	10
1	2021-03-29	speed	20
1	2021-03-30	height	30
1	2021-03-30	temperature	40
2	2021-03-29	height	50
2	2021-03-29	acceleration	60
2	2021-03-29	distance	70

And want to query so I Group By device_id and date, and create columns by variable_name, so the table expected is:

device_id	date	height	speed	temperature	acceleration	distance
1	2021-03-29	10	20	0	0	0
1	2021-03-30	30	0	40	0	0
2	2021-03-29	50	0	0	60	70

Any idea of how to do this?

CodePudding user response：

in addition to crosstab, there is a more direct way through case operator

Select device_id, date, 
       Sum(Case When variable_name='height' Then pulses Else 0 End) As height,
       Sum(Case When variable_name='speed' Then pulses Else 0 End) As speed,
       Sum(Case When variable_name='temperature' Then pulses Else 0 End) As temperature,
       Sum(Case When variable_name='acceleration' Then pulses Else 0 End) As acceleration,
       Sum(Case When variable_name='distance' Then pulses Else 0 End) As distance
From Tbl
Group by device_id, date
Order by device_id, date

Data Output:

device_id| date                 | height | speed | temperature | acceleration | distance
_________|______________________|________|_______|_____________|______________|_________
        1| 2021-03-29 00:00:00  |      10|     20|            0|             0|        0
        1| 2021-03-30 00:00:00  |      30|      0|           40|             0|        0
        2| 2021-03-29 00:00:00  |      50|      0|            0|            60|       70