Crosstab function in Postgres returning a one row output when I expect multiple rows

Question

I currently have a table m of the following format:

id    scenario    period    ct
2        1          1       1  
2        1          2       1
2        1          3       1
2        1          4       1
2        2          1       1
2        2          2       1
2        2          3       1
2        2          4       1
2        3          1       1
2        3          2       1
2        3          3       1
2        3          4       1

I want to create the following table:

id    scenario    period    1    2    3    4
2        1          1       1
2        1          2            1
2        1          3                 1
2        1          4                      1
2        2          1       1
2        2          2            1
2        2          3                 1
2        2          4                      1
2        3          1       1
2        3          2            1
2        3          3                 1
2        3          4                      1

The tablefunc extension has been created in my Postgres database already. I'm currently trying to use the crosstab() function to complete the pivot. However, I'm getting a table that looks like the following:

id    scenario    period    1    2   3    4 
2        1          1       1    1   1    1

The query I tried:

SELECT * FROM crosstab(
        'SELECT id, scenario, period, ct FROM m 
            ORDER BY 1',
         'SELECT DISTINCT period FROM m 
            ORDER BY 1')
AS (id, scenario, period, 1, 2, 3, 4);

The query you present is plain invalid. (column definition list is missing data types). What's the actual query you tried? — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 3, 2015 at 1:37
My dataset is much larger, but I wanted a simpler example to show. I believe the more correct query would be: SELECT * FROM crosstab( 'SELECT id, scenario, period, ct FROM m ORDER BY 1', 'SELECT DISTINCT period FROM m ORDER BY 1') AS (id int, scenario int , period int, "1" int, "2" int, "3" int, "4" int); — Stevie Kobori
– Stevie Kobori, Commented Oct 3, 2015 at 1:49

Community · Accepted Answer · 2017-05-23 12:31:49Z

5

This query generates your desired output:

SELECT id, scenario, period, p1, p2, p3, p4  -- all except aux column rn
FROM   crosstab(
  'SELECT row_number() OVER (ORDER BY id, scenario, period)::int AS rn
        , id, scenario, period, period, ct
   FROM   m
   ORDER  BY 1'
, 'VALUES (1), (2), (3), (4)'
   ) AS (rn int, id int, scenario int, period int, p1 int, p2 int, p3 int, p4 int);

Two special difficulties:

You don't have a single unique column for the row_name yet. I use row_number() to generate the surrogate key: rn. I removed it from the outer SELECT to match your desired result.
The way you tried it, id is taken to be the row_name and all input rows are aggregated into a single output row.
You want additional columns (scenario and period) in the result, which must come after the row_name and before the category. You must list period twice to get the original column additionally - redundant as though it may seem.

Basics:

PostgreSQL Crosstab Query

Related to this particular case:

Pivot on Multiple Columns using Tablefunc

Typically, you would have a query like this:

SELECT id, scenario, p1, p2, p3, p4  -- all except aux column rn
FROM   crosstab(
  'SELECT rank() OVER (ORDER BY id, scenario)::int AS rn
        , id, scenario, period, ct
   FROM   m
   ORDER  BY 1'
, 'VALUES (1), (2), (3), (4)'
   ) AS (rn int, id int, scenario int, p1 int, p2 int, p3 int, p4 int);

With an output like this:

id   scenario   p1   p2   p3   p4
2    1          1    1    1    1
2    2          1    1    1    1
2    3          1    1    1    1

Note the use of rank() instead of row_number() to group same combinations of (id, scenario) together.
The result makes more sense if counts are not all 1.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Oct 3, 2015 at 1:54

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stevie Kobori Over a year ago

Hi Erwin, Thanks for answering my question! I have a follow-up question regarding the crosstab function. It turns out I want my crosstab function to specify value columns by selecting IDs from another table (the second parameter of my crosstab function would be "SELECT id_prod FROM prod;"). How might I alter the end of my query (after AS (rn int,... ) ) to correctly show the results from this change?

Collectives™ on Stack Overflow

Crosstab function in Postgres returning a one row output when I expect multiple rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related