Using a crosstab in a composite key (multiple columns) query
I recently switched from SQL Server to PostgreSQL and tried to find the equivalent of the pivot function. I cannot get the desired result using a crosstab that I was able to achieve with 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 the 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 - 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 line instead of two lines. Anyway, to overcome this limitation in postgres.
source to share
Using ARRAY to Solve the Composite Key Problem
I think the real problem you are having is yours sub_location
is part of your main id (name) for the purposes of the cross. And not that the crosstab calls the extra column.
The "extra" columns are expected to be the same for all rows with the same row_name value.
So, in essence, the composite keys that make up the name must be serialized by the user. You can still do this serialization to SQL ARRAY
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)
Using your sublocation having the actual location in it
Now, since there is location data in the subdirectory of your particular case, we can make it even shorter by switching the order. I wouldn't have a subitem stored in a table using 100_
, but we can use that here. To be clear, it won't work if location: 100, sublocation: 'A'
that I would keep.
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 removes the complexity of the call ARRAY
.
Simplification for your use case
We can also just remove the "location" at this point, or switch the order in the parent request.
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 CREATE EXTENSION tablefunc;
Of course, this is completely subjective, simpler or not, than the non-cross-version version.
source to share