ColumnA | ColumnB | Codes | ColumnD | ColumnE |
---|---|---|---|---|
1 | A | Type1 | Text1 | Yes |
1 | A | Type1 | Text2 | Yes |
1 | B | Type2 | Text3 | Yes |
2 | C | Type1 | Text4 | No |
2 | D | Type2 | Text5 | No |
2 | E | Type3 | Text6 | No |
I want to be able to pivot on Column B to have the view look something like the below:
ColumnA | Type1 - ColumnB | Type2 - ColumnB | Type3 - ColumnB | Type2 - ColumnD | Type2 - ColumnD | Type3 - ColumnD | Column E |
---|---|---|---|---|---|---|---|
1 | A | B | NA | Text1 & Text2 | Text3 | NA | Yes |
2 | C | D | E | Text4 | Text5 | Text6 | No |
Pleas note column E is associated with Column A so can only 1 value for each recording Column A
Use the DENSE_RANK
function to number the rows according to the columnb
values for each columna
and then PIVOT
:
SELECT columna,
"1_TYPE_B" AS type_1_b,
"2_TYPE_B" AS type_2_b,
"3_TYPE_B" AS type_3_b,
"1_TYPE_D" AS type_1_d,
"2_TYPE_D" AS type_2_d,
"3_TYPE_D" AS type_3_d,
columne
FROM (
SELECT ColumnA, ColumnB, ColumnD, ColumnE,
DENSE_RANK() OVER (PARTITION BY columnA ORDER BY columnB) AS rnk
FROM table_name t
)
PIVOT (
MAX(columnB) AS type_b,
LISTAGG(columnD, ' & ') WITHIN GROUP (ORDER BY columnD) AS type_d
FOR rnk IN (
1 AS "1", 2 AS "2", 3 AS "3"
)
);
Which, for the sample data:
CREATE TABLE table_name (ColumnA, ColumnB, Codes, ColumnD, ColumnE) AS
SELECT 1, 'A', 'Type1', 'Text1', 'Yes' FROM DUAL UNION ALL
SELECT 1, 'A', 'Type1', 'Text2', 'Yes' FROM DUAL UNION ALL
SELECT 1, 'B', 'Type2', 'Text3', 'Yes' FROM DUAL UNION ALL
SELECT 2, 'C', 'Type1', 'Text4', 'No' FROM DUAL UNION ALL
SELECT 2, 'D', 'Type2', 'Text5', 'No' FROM DUAL UNION ALL
SELECT 2, 'E', 'Type3', 'Text6', 'No' FROM DUAL;
Outputs:
COLUMNA | TYPE_1_B | TYPE_2_B | TYPE_3_B | TYPE_1_D | TYPE_2_D | TYPE_3_D | COLUMNE |
---|---|---|---|---|---|---|---|
1 | A | B | null | Text1 & Text2 | Text3 | null | Yes |
2 | C | D | E | Text4 | Text5 | Text6 | No |