postgresqlpivotgroupingpivot-tablecrosstab

PostgreSQL crosstab query with multiple columns and week day

I'm trying to create a pivot by grouping several columns: user id, name, week number and the name of the day. The current request does not give the desired result. I need help.

Here is my table:

user_id name    week_number day_name    price
2       Luc     8           Sunday      10
2       Luc     8           Monday      15
2       Luc     8           Tuesday     8
2       Luc     8           Wednesday   2
2       Luc     8           Thursday    9
2       Luc     8           Friday      9
2       Luc     8           Saturday    11
2       Luc     9           Saturday    1
2       Luc     9           Friday      13
3       Mathieu 8           Sunday      22
3       Mathieu 8           Monday      13
3       Mathieu 8           Tuesday     9
3       Mathieu 8           Wednesday   3

Here is my current request:

SELECT *
FROM   crosstab(
'SELECT user_id, name, week_number,day_name,price
FROM   table_1
ORDER  BY 1,2,3,4' 
) AS ct (
"user_id" integer,
"day_name" text,
"Sunday" integer,   
"Monday" integer,   
"Tuesday" integer,  
"Wednesday" integer,    
"Thursday" integer, 
"Friday" integer,   
"Saturday" integer
);

And here are the results I want to get.

enter image description here

Solution

  • You could just use conditional aggregation:

    SELECT user_id, name, week_number
       MAX(price) FILTER (WHERE day_name = 'Sunday') as Sunday,
       MAX(price) FILTER (WHERE day_name = 'Monday') as Monday,
       MAX(price) FILTER (WHERE day_name = 'Tuesday') as Tuesday,
       MAX(price) FILTER (WHERE day_name = 'Wednesday') as Wednesday,
       MAX(price) FILTER (WHERE day_name = 'Thursday') as Thursday,
       MAX(price) FILTER (WHERE day_name = 'Friday') as Friday,
       MAX(price) FILTER (WHERE day_name = 'Saturday') as Saturday
FROM table_1
GROUP BY user_id, name, week_number
ORDER BY user_id, name, week_number;

    EDIT:

    You can write the same logic without FILTER:

           MAX(CASE WHEN day_name = 'Sunday' THEN price END) as Sunday,