Home > Back-end >  pivot table with multiple value columns in postgres
pivot table with multiple value columns in postgres

Time:10-14

I need a query which will help me to get data as described below

I have a table as followed

ID date STATUS TIME09_10 TIME10_11 TIME11_12 TIME12_13
1 2021-09-01 RUN 30 60 45 0
2 2021-09-01 WALK 15 0 o 30
3 2021-09-01 STOP 15 0 15 30

I want this data to be in below format. From the above table , value columns name has to be replaced with hour and join with date column. I would really appreciate someone’s help. I am using postgres sql database.

date & time run walk stop
2021-09-01 09:00 30 15 15
2021-09-01 10:00 60 0 0
2021-09-01 11:00 45 0 15
2021-09-01 12:00 0 30 30

CodePudding user response:

You can use the crosstab(source_sql text, category_sql text) function. You need to install the tablefunc extension:

create extension if not exists tablefunc;

Read about the extension in the documentation.

The function expects data in the three-column format (row_name, category, value). In this case they are date time, status and duration.

select 
    date  '8 hour'::interval  '1 hour'::interval* i as hour,
    status,
    (array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i

        hour         | status | duration
--------------------- -------- ----------
 2021-09-01 09:00:00 | RUN    |       30
 2021-09-01 09:00:00 | WALK   |       15
 2021-09-01 09:00:00 | STOP   |       15
 2021-09-01 10:00:00 | RUN    |       60
 2021-09-01 10:00:00 | WALK   |        0
 2021-09-01 10:00:00 | STOP   |        0
 2021-09-01 11:00:00 | RUN    |       45
 2021-09-01 11:00:00 | WALK   |        0
 2021-09-01 11:00:00 | STOP   |       15
 2021-09-01 12:00:00 | RUN    |        0
 2021-09-01 12:00:00 | WALK   |       30
 2021-09-01 12:00:00 | STOP   |       30
(12 rows)

Pass the query as the first argument to the function:

select *
from crosstab(
    $source$
        select 
            date  '8 hour'::interval  '1 hour'::interval* i as hour,
            status,
            (array[time09_10, time10_11, time11_12, time12_13])[i] as duration
        from my_table
        cross join generate_series(1, 4) i
    $source$,
    $category$
        values('RUN'), ('STOP'), ('WALK')
    $category$
) as (hour timestamp, run int, stop int, walk int)
        hour         | run | stop | walk
--------------------- ----- ------ ------
 2021-09-01 09:00:00 |  30 |   15 |   15
 2021-09-01 10:00:00 |  60 |    0 |    0
 2021-09-01 11:00:00 |  45 |   15 |    0
 2021-09-01 12:00:00 |   0 |   30 |   30
(4 rows)

There is a nice alternative if you do not want to use the extension. Convert the first query results to the expected output with the jsonb function:

select 
    hour, 
    (activities->>'RUN')::int as run,
    (activities->>'STOP')::int as stop,
    (activities->>'WALK')::int as walk
from (
    select hour, jsonb_object_agg(status, duration) as activities
    from (
        select 
            date  '8 hour'::interval  '1 hour'::interval* i as hour,
            status,
            (array[time09_10, time10_11, time11_12, time12_13])[i] as duration
        from my_table
        cross join generate_series(1, 4) i
        ) s
    group by hour
    ) s
order by hour

Test the jsonb solution in Db<>fiddle.

  • Related