Rotating a table with multiple value columns

Question

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.

Answer 1

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?

Answer 2

Crosstab queries with a large or unknown (dynamic) number of columns have the issue that you need to enumerate these columns in the query.

There are some ways to avoid that:

• generate the query that pivots all columns with dynamic SQL. In that case, two round-trips to the server are required: one to generate the query, the other to run it.

• pivoting client-side, like psql can do with the \crosstabview command,

With a generated pivot query

In the case of the question, the generated query should look like this:

  SELECT id,
  sum(case when week=1 then param1 end) AS w1param1,
  sum(case when week=1 then param2 end) AS w1param2,
  sum(case when week=1 then param3 end) AS w1param3,
  sum(case when week=2 then param1 end) AS w2param1,
  ... repeated until w50param3 or somesuch
  FROM tablename GROUP BY id ORDER BY id;

Note that crosstab() is not used with this method.

This target query could be generated as a dynamic statement with the following query, which is basically a cross product over the week numbers and (1,2,3) with some bits of SQL around:

SELECT concat('SELECT id,',
 (SELECT string_agg(clause, ',') FROM (
  SELECT format('sum(case when week=%s then param%s end) AS w%sparam%s',
 week, p, week, p) AS clause
FROM (SELECT DISTINCT week FROM tablename) s,
      generate_series(1,3) as p
ORDER BY week,p
  ) clauses),
  ' FROM tablename GROUP BY id ORDER BY id'
) AS pivot_query;

In a programming language, if you execute this query, fetch the result (a single-row, single-column string) and execute that result as an SQL query, it will produce the expected pivoted data with the 150+ columns.

In psql, this can be done through \gset and a variable

 =# SELECT concat(
  ... etc...
  ) AS pivot_query \gset

 =# :pivot_query \g

To save the results into a file without padding and decoration, use these settings:

=# \pset footer off   \o /path/to/file.txt   \pset format unaligned

The field separator can also be set with \pset fieldsep or \F

Once the results are in the file, use \o alone to redirect the output back to the terminal.

---

With a client-side pivot (psql)

A client-side, psql-only solution is more straightforward with \crosstabview (psql 9.6+).

The query would be quite different: it must first "unpivots" (with the UNION ALL subquery) the columns param1..2..3 to get the desired column names in the 2nd column, the corresponding values of these parameters in a 3rd column, and the desired column order for the pivoted data in a 4th column.

This would look like:

SELECT id, 'w' || week || 'param' || p, val,
  row_number() over (order by week,p) as rn
FROM (                                                                         
SELECT id, week, 1 AS p, param1 AS val FROM tablename
UNION ALL                                          
select id, week, 2 AS p, param2 AS val FROM tablename
UNION ALL
select id, week, 3 AS p, param3 AS val FROM tablename
) s ORDER BY id \crosstabview  1 2 3 4

To redirect to a file, it's the same as the previous solution. You probably want to remove the decoration, padding, footer and choose a separator.