Question 89373 in PostgreSQL Question posted in
The official documentation can be found here.

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

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);
Tags:

2

Answers


  1. 0

    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;

    enter image description here

    Login or Signup to reply.
  2. 0

    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:

    This optimization will work automatically in Postgres 16. 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search