Postgres Crosstab function returns single line output when I expect multiple lines

Question

I currently have table m 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 is already created in my Postgres database. I am currently trying to use the crosstab() function to complete a fulcrum. However, I get a table that looks like this:

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

The request 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); 

Answer 1

This query generates the desired result:

 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 do not yet have a unique column for the row name. I use row_number() to generate a surrogate key: rn . I removed it from an external SELECT according to your desired result.
  The way you tried it, id taken as the name of the line, and all input lines are aggregated into one output line.

• As a result, you will get additional columns ( scenario and period ), which should appear after the row_name and before the category. You must list period twice to get the original column extra - redundantly, as if it might seem.

The basis:

• PostgreSQL Cross Forward Request

In connection with this particular case:

• Rotate multiple columns using Tablefunc

---

As a rule, you will have this request:

 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 similar to 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 the same combinations (id, scenario) together.
The result makes sense if the calculations are not all 1 .