PostgreSQL – Crosstab Query on Multiple Columns

pivotpostgresql

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

Best Answer

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)

Related Solutions

PostgreSQL Pivot with Multiple Columns – Using CROSSTAB

Yes it is possible, here is the solution:

WITH cte AS 
    ( SELECT  * 
      FROM CROSSTAB 
          ( 'SELECT  country_code, country_name, year, 
                     deflator || '',''|| source
             FROM deflator 
             ORDER BY 1;', 
             $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
          ) AS "ct" ( "country_code" SMALLINT, 
                      "country_name" TEXT , 
                      "2014" text, "2015" text, "2016" text
                    )
    )
SELECT country_code, country_name, 
       split_part("2014",',',1) AS "2014", 
       split_part("2014",',',2) AS "2014_source", 
       split_part("2015",',',1) AS "2015", 
       split_part("2015",',',2) AS "2015_source", 
       split_part("2016",',',1) AS "2016", 
       split_part("2016",',',2) AS "2016_source" 
FROM cte ;

PostgreSQL Crosstab Query Help – How to Create Pivot Tables

You don't need the GROUP BY in a pivot... And, if you put that WHERE clause in there you're filtering out stuff you need.

SELECT *
FROM crosstab(
  $$
    SELECT game.team_name::text, game.match_id, game.sections_won
    FROM game
    ORDER by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

  Team  | Match 6 | Match 7 | Match 8 | Match 19 
--------+---------+---------+---------+----------
 Team A |       4 |       4 |       4 |        4
 Team B |       1 |       1 |       1 |        1
(2 rows)

As to why you need the ORDER BY, from the docs

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.

Related Question