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

i have table m of 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 create 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 postgres database already. i'm trying use crosstab() function complete pivot. however, i'm getting table looks following:

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

the query tried:

select * crosstab(         'select id, scenario, period, ct m              order 1',          'select distinct period m              order 1') (id, scenario, period, 1, 2, 3, 4); 

this query generates desired output:

select id, scenario, period, p1, p2, p3, p4  -- except aux column rn   crosstab(   'select row_number() on (order id, scenario, period)::int rn         , id, scenario, period, period, ct      m    order  1' , 'values (1), (2), (3), (4)'    ) (rn int, id int, scenario int, period int, p1 int, p2 int, p3 int, p4 int); 

two special difficulties:

1. you don't have single unique column row_name yet. use row_number() generate surrogate key: rn. removed outer select match desired result.
   way tried it, id taken row_name , input rows aggregated single output row.

2. you want additional columns (scenario , period) in result, must come after row_name , before category. must list period twice original column additionally - redundant though may seem.

basics:

• postgresql crosstab query

related particular case:

• pivot on multiple columns using tablefunc

---

typically, have query this:

select id, scenario, p1, p2, p3, p4  -- except aux column rn   crosstab(   'select rank() on (order id, scenario)::int rn         , id, scenario, period, ct      m    order  1' , 'values (1), (2), (3), (4)'    ) (rn int, id int, scenario int, p1 int, p2 int, p3 int, p4 int); 

with output 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 use of rank() instead of row_number() group same combinations of (id, scenario) together.
result makes more sense if counts not 1.