My table:
CREATE TABLE tbl(
id int,
fiber varchar2(40),
pct int);
insert into tbl (id, fiber, pct) values ('1','Cotton','60');
insert into tbl (id, fiber, pct) values ('1','Poly','40');
insert into tbl (id, fiber, pct) values ('2','Elastane','30');
insert into tbl (id, fiber, pct) values ('2','Cotton','50');
insert into tbl (id, fiber, pct) values ('2','Spandex','20');
I'm trying to write a SQL query with PIVOT to put these into columns, so my SQL result looks something like this:
ID Fiber1 Pct1 Fiber2 Pct2 Fiber3 Pct3
-------------------------------------------------
1 Cotton 60 Poly 40
2 Elastane 30 Cotton 50 Spandex 20
I've never used Pivot before. All of the examples I found have the columns explicitly listed out in the Pivot query. The problem is, the fiber column has dozens of different values, so hard coding them in the query isn't practical.
Curious if there's a way to make the columns generic like this and get the data into rows.
Thank you!
Use the ROW_NUMBER() analytic function to number the rows for each id and then PIVOT:
SELECT id,
"1_FIBER" AS fiber1,
"1_PCT" AS pct1,
"2_FIBER" AS fiber2,
"2_PCT" AS pct2,
"3_FIBER" AS fiber3,
"3_PCT" AS pct3
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY fiber) AS rn
FROM tbl t
)
PIVOT (
MAX(fiber) AS fiber,
MAX(pct) AS pct
FOR rn IN (1, 2, 3)
)
Or, use the ROW_NUMBER() analytic function and conditional aggregation:
SELECT id,
MAX(CASE rn WHEN 1 THEN fiber END) AS fiber1,
MAX(CASE rn WHEN 1 THEN pct END) AS pct1,
MAX(CASE rn WHEN 2 THEN fiber END) AS fiber2,
MAX(CASE rn WHEN 2 THEN pct END) AS pct2,
MAX(CASE rn WHEN 3 THEN fiber END) AS fiber3,
MAX(CASE rn WHEN 3 THEN pct END) AS pct3
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY fiber) AS rn
FROM tbl t
)
GROUP BY id;
Which, for the sample data, both output:
| ID | FIBER1 | PCT1 | FIBER2 | PCT2 | FIBER3 | PCT3 |
|---|---|---|---|---|---|---|
| 1 | Cotton | 60 | Poly | 40 | null | null |
| 2 | Cotton | 50 | Elastane | 30 | Spandex | 20 |