Pivot with column name in Postgres

Question

I have the following table tbl:

column1 | column2  | column 3
-----------------------------------
1       | 'value1' |  3
2       | 'value2' |  4

How to do "pivot" with column names to produce output like:

column1 |      1   |   2
column2 | 'value1' |'value2'
column3 |      3   |   4

If your table will have 10M records, what should be the output? — astentx, May 23 '21 at 20:13
@astentx the table has a few records no more than 10, but lots of columns. — Some Name, May 23 '21 at 20:14
You cannot do this in SQL. The columns in the result set have different types on different rows. (Well, there are things you can do, but it is not a natural SQL query.) — Gordon Linoff, May 23 '21 at 20:25
Oh, and of course the column in SQL result set can have only one datatype, so to do such transposition you need to do conversion to some generic type like varchar or maybe JSON. But you definitely lose the original types — astentx, May 23 '21 at 20:25

Erwin Brandstetter · Accepted Answer · 2021-05-24T02:12:12.033

As has been commented, the issue of data types is undefined in the question.

If you are OK with all result columns being type text (every data type can be converted to text), you can use one of these:

Plain SQL

WITH cte AS (
   SELECT nu.*
   FROM   tbl t
   , LATERAL (
      VALUES
        (1, t.column1::text)
      , (2, t.column2)
      , (3, t.column3::text)
      ) nu(rn, c)
   )
SELECT *
FROM   (TABLE cte OFFSET 0 LIMIT 3) c1
JOIN   (TABLE cte OFFSET 3 LIMIT 3) c2 USING (rn);

The same with useful column names:

WITH cte AS (
   SELECT nu.*
   FROM   tbl t
   , LATERAL (
      VALUES
        ('column1', t.column1::text)
      , ('column2', t.column2)
      , ('column3', t.column3::text)
      ) nu(rn, c)
   )
SELECT * FROM (
   SELECT *
   FROM   (TABLE cte OFFSET 0 LIMIT 3) c1
   JOIN   (TABLE cte OFFSET 3 LIMIT 3) c2 USING (rn)
   ) t (key, row1, row2);

Works in any modern version of Postgres.
The SQL string has to be adapted to the number of rows and columns. See fiddles below!

Using a document type as stepping stone

Makes for shorter code.
With many rows and many columns, performance of the SQL solution may scale better because the intermediate derived table is smaller. (The thread is limited as you can't have more than ~ 1600 table columns in Postgres.)

Since everything is converted to text anyway, hstore seems most efficient. See:

Key value pair in PostgreSQL

SELECT key
     , arr[1] AS row1
     , arr[2] AS row2
FROM  (
   SELECT x.key, array_agg(x.value) AS arr
   FROM   tbl t, each(hstore(t)) x
   GROUP  BY 1
   ) sub
ORDER  BY 1;

Technically speaking we would have to enforce the right sort order when in array_agg(), but that should work without explicit ORDER BY. To be absolutely sure you can add one: array_agg(x.value ORDER BY t.ctid) Using ctid for lack of information.

You can do the same with JSON functions in (Postgres 9.3+). Just replace each(hstore(t) with json_each_text(row_to_json(t). The rest is identical.

These fiddles demonstrate how to scale each query:

Original example with 2 rows of 3 columns:
db<>fiddle here

Scaled up to 3 rows of 4 columns:
db<>fiddle here

Pivot with column name in Postgres

1 Answers1

Plain SQL

Using a document type as stepping stone