Rotating a table with multiple value columns

I'm not sure if this can be done with crosstab(). I've been using this questions/answer as a reference, but am having difficulty putting together the query.

My table looks like this, just that I have over 50 distinct values in the week column:

+------+----+--------+---------+--------+
| week | id | param1 | param2  | param3 |
+------+----+--------+---------+--------+
|    1 |  1 |     13 |      10 |     12 |
|    1 |  2 |     12 |      11 |     44 |
|    2 |  1 |     34 |      33 |      3 |
|    2 |  2 |      3 |      44 |      3 |
+------+----+--------+---------+--------+

I would like to rotate it such that it looks like this:

+----+----------+-----------+----------+----------+----------+----------+-----+
| id | w1param1 | w1param2  | w1param3 | w2param1 | w2param2 | w2param3 | ... |
+----+----------+-----------+----------+----------+----------+----------+-----+
|  1 |       13 |        10 |       12 |       34 |       33 |        3 | ... |
|  2 |       12 |        11 |       44 |        3 |       44 |        3 | ... |
+----+----------+-----------+----------+----------+----------+----------+-----+

where the week column is used to number the parameter columns, and the rows become columns.

The (2nd form of the) crosstab() function expects these columns as input:

• 1 row_name column

• (0-n) extra columns

• 1 category column

• 1 value column

See:

• Pivot on Multiple Columns using Tablefunc

Your specific difficulty is that you are trying to process 3 value columns at once (param1, param2, param3). Your input table is already "half pivoted". There are various ways to solve this. Joining three crosstab queries is probably cleanest. Demonstrating for 5 weeks:

SELECT *
FROM   crosstab(
     'SELECT id, week, param1
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct1 (id int, w1p1 int, w1p2 int, w1p3 int, w1p4 int, w1p5 int)
JOIN   crosstab(
     'SELECT id, week, param2
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct2 (id int, w2p1 int, w2p2 int, w2p3 int, w2p4 int, w2p5 int) USING (id)
JOIN   crosstab(
     'SELECT id, week, param3
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct3 (id int, w3p1 int, w3p2 int, w3p3 int, w3p4 int, w3p5 int) USING (id)

dbfiddle here

[INNER] JOIN is safe, since all instances are guaranteed to return the same week ids. Else we'd use FULL JOIN.

With over 50 weeks, you get over 150 columns. Is that really what you want?

StackExchange Database Administrators Q#197501, answer score: 4