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:
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;
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
' 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(
) 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
Once the results are in the file, use
\o alone to redirect the output back to the terminal.
With a client-side pivot (psql)
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
SELECT id, week, 1 AS p, param1 AS val FROM tablename
select id, week, 2 AS p, param2 AS val FROM tablename
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.