CODEWITHSUNDEEP
sqlpostgresqlcrosstab
jobin
jobin

Reputation: 2798

Using crosstab to convert rows to columns fails

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.

Upvotes: 1

Views: 1708

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656521

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:

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

Upvotes: 2

Ye Win
Ye Win

Reputation: 2098

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"

Upvotes: 0

mucio
mucio

Reputation: 7119

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

Upvotes: 1

Related Questions