Postgres crosstab query on multiple columns I am trying to build a crosstab query on multiple columns.

To accomplish that I first created a type:

create type product_status as (product varchar(255), status varchar(255))


Then I created my query:

select pivot.*
from crosstab(
    $$
    select
        uniqueid,
        (product, status)::product_status,
        count(*)
    from policycase
    inner join policy
    on policy.id = policycase.selected_id
    group by 1,2
    order by 1,2
    $$,
    $$
    values(
--          ('(HOUSEFIRE,HISTORICAL)'::product_status),
--          ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)
    )
    $$
) as pivot (
    uniqueid bigint,
--     housefire_hist smallint,
--     housecontent_hist smallint,
    liability_arch smallint
)


Which works and gives me correct results back. However when I uncomment the commented lines in the example above, I get the following error message:


  [Exception, Error code 0, SQLState 42601] ERROR: provided "categories"
  SQL must return 1 column of at least one row Line 1, column 1


Two tables are used:

The policycase has a id, selected_id and a status

The policy has a id, uniqueid and product
The values clause here:

values(
          ('(HOUSEFIRE,HISTORICAL)'::product_status),
          ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)
    )


produces a single line with 3 columns, whereas you want 3 lines with a single column, as in:

values   ('(HOUSEFIRE,HISTORICAL)'::product_status),
         ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)