Using crosstab to convert rows to columns fails

Question

I have a postgres table with the following format:

id            key             value
-------------------------------------
a              1               p
b              2               q
c              3               r
a              2               s

I want to convert it into the following format:

id            1             2            3
--------------------------------------------
a             p             s
b                           q
c                                        r

I am trying to using the following crosstab() query to do this:

create extension tablefunc;

select * from crosstab(
'select id, key, value
from table
order by 1,2')
as ct(id text, key integer, value text);

However, it fails with the following exception:

ERROR:  return and sql tuple descriptions are incompatible
********** Error **********

ERROR: return and sql tuple descriptions are incompatible
SQL state: 42601

What am I missing here?

Update: There are around 25 keys in the table.

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

You must provide a second parameter with the list of possible values to allow for missing values in each resulting row:

SELECT * 
FROM   crosstab(
         'SELECT id, key, value
          FROM   tbl
          ORDER  BY 1, 2'
       , 'SELECT generate_series(1,25)'  -- assuming your key is type integer
       ) AS ct(id text
       , "1" text, "2" text, "3" text, "4" text, "5" text
       , "6" text, "7" text, "8" text, "9" text, "10" text
       , "11" text, "12" text, "13" text, "14" text, "15" text
       , "16" text, "17" text, "18" text, "19" text, "20" text
       , "21" text, "22" text, "23" text, "24" text, "25" text);

Detailed explanation:

PostgreSQL Crosstab Query

If you get tired of typing the lengthy column definition list, consider this related (advanced) answer:

Dynamic alternative to pivot with CASE and GROUP BY

edited May 23 '17 at 12:12

Community

1
1

answered Dec 15 '14 at 11:36

Erwin Brandstetter

605,456
145
1,078
1,228

It returns empty rows for values when I add `'SELECT generate_series(1, 25)'`. – jobin Dec 15 '14 at 11:50
@i08in: Define "empty row". All columns NULL? If you can't resolve this, start by providing the exact table definition of `tbl` in your question. – Erwin Brandstetter Dec 15 '14 at 12:04
No, only the entries in the columns "1", "2", "3", .... "25" are empty. key is integer. – jobin Dec 15 '14 at 12:06
@i08in: That's per design and happens for any `id` that shows up in your base table but without any matching `key` (number between 1 and 25 in the example). – Erwin Brandstetter Dec 15 '14 at 12:16
Thanks! The problem was the sequence was not in order from 1-25. My bad. You got it solved :) – jobin Dec 15 '14 at 13:08

score 1 · Answer 2 · answered Dec 15 '14 at 10:46

1

Try this:

SELECT * 
  FROM crosstab(
                'SELECT id, 
                        key, 
                        value
                   FROM table
               ORDER BY 1, 2'
               )
    AS CT(id text, one text, two text, three text);

You need the final four column names in as ct(), check here for more details

answered Dec 15 '14 at 10:46

mucio

7,014
1
21
33

Other than having to explicitly add twenty-five column headers, this seems to be solution. Thanks :) BTW, what is the purpose of the 'order by' here? – jobin Dec 15 '14 at 11:08
@i08in: This solves the immediate cause for the error message, but it's not the solution. You have missing values per result rows in your example, which cannot be solved properly with the 1-parameter form of `crosstab()`. You need the 2-parameter form. – Erwin Brandstetter Dec 15 '14 at 11:39

score 0 · Answer 3 · edited May 23 '17 at 11:57

Please reference with below code.
I hope it will work for you.

SELECT * 
  FROM crosstab(
                'SELECT id, 
                        key, 
                        "value"
                   FROM table                   
               ORDER BY  1,2'
               )
    AS t(id text, "1" text,"2" text, "3" text);

If you are not enough, you can also refer similar problem on this link. PostgreSQL says "return and sql tuple descriptions are incompatible"

Using crosstab to convert rows to columns fails

3 Answers3