CROSSTAB PostgreSQL - Alternative for PIVOT in Oracle

Question

I'm migrating a query of Oracle pivot to PostgreSQL crosstab.

create table(cntry numeric,week numeric,year numeric,days text,day text);
insert into x_c values(1,15,2015,'DAY1','MON');
...
insert into x_c values(1,15,2015,'DAY7','SUN');
insert into x_c values(2,15,2015,'DAY1','MON');
...
                values(4,15,2015,'DAY7','SUN');

I have 4 weeks with 28 rows like this in a table. My Oracle query looks like this:

SELECT * FROM(select * from x_c)
PIVOT (MIN(DAY) FOR (DAYS) IN
   ('DAY1' AS DAY1 ,'DAY2' DAY2,'DAY3' DAY3,'DAY4' DAY4,'DAY5' DAY5,'DAY6' DAY6,'DAY7' DAY7 ));

Result:

cntry|week|year|day1|day2|day3|day4|day4|day6|day7|
---------------------------------------------------
   1 | 15 |2015| MON| TUE| WED| THU| FRI| SAT| SUN|
...
   4 | 18 |2015| MON| ...

Now I have written a Postgres crosstab query like this:

select *
from crosstab('select cntry,week,year,days,min(day) as day
               from x_c
               group by cntry,week,year,days'
             ,'select distinct days from x_c order by 1'
             ) as (cntry numeric,week numeric,year numeric
                  ,day1 text,day2 text,day3 text,day4 text, day5 text,day6 text,day7 text);

I'm getting only one row as output:

  1|17|2015|MON|TUE| ...   -- only this row is coming

Where am I doing wrong?

Erwin Brandstetter · Accepted Answer · 2022-04-20T22:56:57.483

ORDER BY was missing in your original query. The manual:

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row.

More importantly (and more tricky), crosstab() requires exactly one row_name column. Detailed explanation in this closely related answer:

Crosstab splitting results due to presence of unrelated field

The solution you found is to nest multiple columns in an array and later unnest again. That's needlessly expensive, error prone and limited (only works for columns with identical data types or you need to cast and possibly lose proper sort order).

Instead, generate a surrogate row_name column with rank() or dense_rank() (rnk in my example):

SELECT cntry, week, year, day1, day2, day3, day4, day5, day6, day7
FROM   crosstab (
  'SELECT dense_rank() OVER (ORDER BY cntry, week, year)::int AS rnk
        , cntry, week, year, days, day
   FROM   x_c
   ORDER  BY rnk, days'
 , $$SELECT unnest('{DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7}'::text[])$$
   ) AS ct (rnk int, cntry int, week int, year int
          , day1 text, day2 text, day3 text, day4 text, day5 text, day6 text, day7 text)
ORDER  BY rnk;

I use the data type integer for out columns cntry, week, year because that seems to be the (cheaper) appropriate type. You can also use numeric like you had it.

Basics for crosstab queries here:

PostgreSQL Crosstab Query

Thanks Erwin for the edits and duly noted your suggestions. I'll replace this with mine. — user1720827, Dec 27 '17 at 16:52

score 0 · Answer 2 · answered Dec 23 '17 at 03:28

I got this figured out from http://www.postgresonline.com/journal/categories/24-tablefunc

  select year_wk_cntry.t[1],year_wk_cntry.t[2],year_wk_cntry.t[3],day1,day2,day3,day4,day5,day6,day7
   from crosstab('select  ARRAY[country :: numeric,week,year] as t,days,min(day) as day
                            from x_c group by country,week,year,days order by 1,2
                            ','select distinct days from x_c order by 1')
                            as year_wk_cntry (t numeric[],day1 text,day2 text,day3 text,
         day4 text, day5 text,day6 text,day7 text);

thanks!!

CROSSTAB PostgreSQL - Alternative for PIVOT in Oracle

2 Answers2