2

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

2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.