I have an issue that is like a non-aggregating pivot, but I want my table to include multiple results. As an example I start out like this:
ID | OrganizationID | Group | Vendor | Product
1 3 G1 V1 P1
2 3 G1 V2 P2
3 3 G2 V3 P3
4 4 G1 V1 P1
5 4 G1 V1 P2
And need my data to end up like this:
ID | OrganizationID | G1_V1 | G1_V2 | G2_V3
1 3 P1 P2 P3
2 4 P1 NULL NULL
3 4 P2 NULL NULL
The data I am working with is around 7 million rows so self-joining with dynamic sql has proven extremely slow. Is there something easy I'm missing? Thanks in advance!
You can use a common table expression and a set of CASE statements to get the pivot structure you need; this will work identically in most RDBMS's.
WITH cte AS (
SELECT id, organizationid,
CONCAT([Group],'_',Vendor) col,
product,
ROW_NUMBER() OVER (
PARTITION BY organizationid,[group],vendor
ORDER BY product) rn
FROM myTable
)
SELECT
organizationid,
MAX(CASE WHEN col='G1_V1' THEN product ELSE NULL END) G1_V1,
MAX(CASE WHEN col='G1_V2' THEN product ELSE NULL END) G1_V2,
MAX(CASE WHEN col='G2_V3' THEN product ELSE NULL END) G2_V3
FROM cte
GROUP BY organizationid, rn;
...or, alternately, you can use TSQL's regular PIVOT statement to replace the CASE part, which will probably be faster but may not work in all RDBMS's;
WITH cte AS (
SELECT id, organizationid,
CONCAT([Group],'_',Vendor) col,
product,
ROW_NUMBER() OVER (
PARTITION BY organizationid,[group],vendor
ORDER BY product) rn
FROM myTable
)
SELECT organizationid as organizationid,
[G1_V1], [G1_V2], [G2_V3]
FROM
(SELECT organizationid,rn,col, product FROM cte) AS SourceTable
PIVOT
(
MAX(product) FOR col IN ([G1_V1], [G1_V2], [G2_V3])
) AS PivotTable;
You can produce a similar pivot in PostgreSQL using crosstab, however I don't have a PostgreSQL database with the tablefunc module installed to test on, so can only link to the details.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments