pivot table with multiple value columns in postgres-CodePudding

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.