Using crosstab on a query with a composite key (multiple columns)

Question

I recently switched from SQL Server to PostgreSQL and trying to find equivalent of pivot function. I am not able to get a desired output using crosstab which I was able to achieve using SQL Server.

Sample data.

CREATE TABLE loc
AS
  SELECT location, sub_location, step, amount
  FROM ( VALUES
    ( 100 , '100_A', 'step_1', 2 ),
    ( 100 , '100_A', 'step_2', 7 ),
    ( 100 , '100_A', 'step_3', 6 ),
    ( 100 , '100_B', 'step_1', 5 ),
    ( 100 , '100_B', 'step_2', 8 ),
    ( 100 , '100_B', 'step_3', 9 )
  ) AS t(location, sub_location, step, amount);

I am trying to achieve this below result set.

Location    Sub_location    Step_1  Step_2  Step_3
--------    ------------    ------  ------  ------
100         100_A           2       7       6
100         100_B           5       8       9

I could easily achieve this is MS SQL. And my crosstab query,

Select * from crosstab
    (
     'select location, sub_location, step, amount from loc',
     'select distinct step from loc'
    )
    as final_result(location varchar,sub_location varchar, step_1 int, step_2 int, step_3 int);

I only see one row instead of two row. Anyway to overcome this limitation in postgres.

Answer 1

Using ARRAY to solve the composite key problem

I think the real issue that you're having is that your sub_location is part of your primary identifier (name) for the purposes of cross. And, not what crosstab calls an extra column.

The "extra" columns are expected to be the same for all rows with the same row_name value.

So in essence, composite keys forming a name must be serialized by the user. You can still make this work serializing to a SQL ARRAY of type text[], using ARRAY[location, sub_location]::text[].

SELECT *
FROM crosstab(
  $$ SELECT ARRAY[location, sub_location]::text[], step, amount FROM loc ORDER BY 1, 2, 3; $$,
  $$ SELECT DISTINCT step FROM loc ORDER BY 1; $$
) AS t(location text[], step_1 int, step_2 int, step_3 int );

  location   | step_1 | step_2 | step_3 
-------------+--------+--------+--------
 {100,100_A} |      2 |      7 |      6
 {100,100_B} |      5 |      8 |      9
(2 rows)

Exploiting your sublocation having the actual location in it

Now, because sub-location in your specific case has data of location we can make this even shorter by switching the ordering around. I wouldn't have sub-location stored in the table with 100_, but we can make use of that here. To be clear, this wouldn't work if location: 100, sublocation: 'A' which is the way I would store it.

SELECT *
FROM crosstab(
  $$ SELECT sub_location, location, step, amount FROM loc ORDER BY 1, 2, 3; $$,
  $$ SELECT DISTINCT step FROM loc ORDER BY 1; $$
) AS t(sub_location text, location int, step_1 int, step_2 int, step_3 int );
 sub_location | location | step_1 | step_2 | step_3 
--------------+----------+--------+--------+--------
 100_A        |      100 |      2 |      7 |      6
 100_B        |      100 |      5 |      8 |      9
(2 rows)

This eliminates the complexity of the call to ARRAY though.

Simplifying for your use case

We can also just drop `location at this point or switch the order around in a parent query.

SELECT *
FROM crosstab(
  $$ SELECT sub_location, step, amount FROM loc ORDER BY 1, 2, 3; $$,
  $$ SELECT DISTINCT step FROM loc ORDER BY 1; $$
) AS t(location_full text, step_1 int, step_2 int, step_3 int );

 location_full | step_1 | step_2 | step_3 
---------------+--------+--------+--------
 100_A         |      2 |      7 |      6
 100_B         |      5 |      8 |      9
(2 rows)

Not sure which method above works best for you. Don't forget to CREATE EXTENSION tablefunc; Of course, it's totally subjective whether or not this is easier than the not-crosstab'ed version.