Postgresql - PIVOT - ROWS to column using Crosstab or any other SQL approach

Question

Here is my data

id|col_name|value|
--+--------+-----+
 1|col1    |ABC  |
 2|col2    |DEF  |
 2|col2    |FGH  |
 2|col2    |IJK  |
 3|col3    |MNO  |
 3|col3    |PQR  |
 3|col3    |STU  |
 3|col3    |XYZ  |

And the expected output is

id  Col1  Col2  col3
1    ABC   DEF  MNO
2    NULL  FGH  PQR
2    NULL  IJK  STU
2    NULL  NULL  XYZ
3    NULL  NULL  NULL
3    NULL  NULL  NULL

I tried this query shown here, but I get an exception:

ERROR: return and sql tuple descriptions are incompatible

This is the query I tried:

select 
    *
from 
    crosstab ('select id,col_name,value from hr.temp order by 1,2')
 AS final_result(id int, col1 TEXT, col2 TEXT, col3 TEXT);

The sample data does not add up. `IJK` is in the result twice. The order of rows is undefined. (There is no natural order of rows in a relational table.) Postgres version? Please explain the objective. It's certainly not a plain pivot / crosstab. — Erwin Brandstetter, Mar 16 '23 at 06:24

score 2 · Answer 1 · answered Mar 16 '23 at 06:15

You are not exactly doing PIVOT, but ordering the values in three columns depending on their order, not some grouping criteria. Try something like this:

CREATE TABLE sample_data (
  id integer,
  col_name varchar(10),
  value varchar(10)
);

INSERT INTO sample_data (id, col_name, value)
VALUES
  (1, 'col1', 'ABC'),
  (2, 'col2', 'DEF'),
  (2, 'col2', 'FGH'),
  (2, 'col2', 'IJK'),
  (3, 'col3', 'MNO'),
  (3, 'col3', 'PQR'),
  (3, 'col3', 'STU'),
  (3, 'col3', 'XYZ');

SELECT row_id,
       MAX(CASE WHEN col_name = 'col1' THEN value ELSE NULL END) AS col1,
       MAX(CASE WHEN col_name = 'col2' THEN value ELSE NULL END) AS col2,
       MAX(CASE WHEN col_name = 'col3' THEN value ELSE NULL END) AS col3
FROM
(
    select col_name
          ,value 
          ,ROW_NUMBER() OVER (PARTITION BY col_name ORDER BY id) as row_id
    from sample_data 
) DS
GROUP BY row_id
ORDER BY row_id;

Erwin Brandstetter · Answer 2 · 2023-03-16T07:19:09.857

Certainly not a plain case of crosstab(). This seems to fit the pattern:

WITH cte AS (
   SELECT id, value, row_number() OVER (PARTITION BY id ROWS
UNBOUNDED PRECEDING) AS rn
   FROM   tbl
   )
SELECT *
FROM      (SELECT rn, value FROM cte WHERE id = 1) t1(id, col1)
FULL JOIN (SELECT rn, value FROM cte WHERE id = 2) t2(id, col2) USING (id)
FULL JOIN (SELECT rn, value FROM cte WHERE id = 3) t3(id, col3) USING (id);

fiddle

But it's really just a guess while the question is undefined.

And the order of rows is coincidental while (also) undefined.

With ROWS UNBOUNDED PRECEDING I slipped in a little secrete sauce to make it faster. See:

Find first 3 orders for each customer

This optimization will work automatically in Postgres 16. :)

Postgresql - PIVOT - ROWS to column using Crosstab or any other SQL approach

2 Answers2