PostgreSQL crosstab query with multiple columns and week day

Question

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.

Answer 1

Below SQL uses crosstab function and gives the expected result only with extra column uniuq_id.

SELECT *
FROM   crosstab(
        'SELECT CONCAT(user_id,name,week_number) as unique_id,
             user_id,name,week_number,day_name,price
             FROM table_1
             ORDER BY unique_id',
        'SELECT day_name FROM (VALUES (''Monday''), (''Tuesday''),
            (''Wednesday''), (''Thursday''), (''Friday''),
            (''Saturday''), (''Sunday'')) b(day_name)'
       ) AS ct (
             "unique_id" varchar,
             "user_id" integer,
             "name" varchar,
             "week_number" integer,
             "Monday" integer,  
             "Tuesday" integer,  
             "Wednesday" integer,    
             "Thursday" integer,
             "Friday" integer,  
             "Saturday" integer,
             "Sunday" integer
           );

Explaination

According to Postgresql official document, crosstab functions has 3 sets of parameters

1. crosstab ( sql text ) → setof record
2. crosstab ( source_sql text, category_sql text ) → setof record
3. crosstab ( sql text, N integer ) → setof record

In the question, you are using actually the first crosstab, and it expects the sql text to return only 3 columns with specific meaning:

row_name    cat    value
----------+-------+-------
row1      cat1    val1
row1      cat2    val2
row2      cat1    val3

And crosstab pivots above table to below format:

row_name    cat1    cat2
----------+-------+-------
row1        val1    val2
row2        val3    null

The SQL in the question however returns 5 columns, which leads to syntax error.

In this case, we need to use second form of crosstab:

crosstab(source_sql, category_sql)

source_sql could return more than 3 columns only following below rules:

• First column is row_id
• Last 2 columns are category columns and value columns
• Extra columns are middle ones

In order to compose such source_sql, using below statements:

SELECT CONCAT(user_id,name,week_number) as unique_id,
      user_id,name,week_number,day_name,price
FROM table_1
ORDER BY unique_id

The category_sql returns the values that we expect to transform to columns, and below statements just return week days in expected order.

SELECT day_name FROM (VALUES ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday'), ('Sunday')) b(day_name)

To sum everything up, we get complete SQL in the first place.

Answer 2

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,