Postgresql Group By and create columns for different values

Question

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?

What's the expected to happen if some later adds a color variable_name? Ignore, or automatically add a color column to the result? — jarlh
– jarlh, Commented Dec 16, 2021 at 14:12
Ignore, or I would have to mannually add the new variable_name as another column — Tomás Riquelme Gajardo
– Tomás Riquelme Gajardo, Commented Dec 16, 2021 at 14:15
You can use case expressions to do conditional aggregation. — jarlh
– jarlh, Commented Dec 16, 2021 at 14:26

Anton Grig · Accepted Answer · 2021-12-16 14:41:03Z

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

@TOMAS RIQUELME GAJARDO You are welcome. Aggregation should be used, because otherwise you will get as many rows for the device_id and date values as there are different variable names.

Collectives™ on Stack Overflow

Postgresql Group By and create columns for different values

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related