I’m trying to convert rows into columns using the following sample:
LVL | COL_VALUE | TABLE_SRC |
---|---|---|
16 | INT: ADDRESS_LINE_2:NULL | INT |
16 | BASE: ADDRESS_LINE_2:X | BASE |
17 | INT: ADDRESS_LINE_3:NULL | INT |
17 | BASE: ADDRESS_LINE_3:X | BASE |
The output should be:
INT | BASE |
---|---|
INT: ADDRESS_LINE_2:NULL | BASE: ADDRESS_LINE_2:X |
INT: ADDRESS_LINE_3:NULL | BASE: ADDRESS_LINE_3:X |
The COL_VALUE with the same LVL should be in 1 row
I tried using PIVOT but it returns only 1 row because of the aggregate function
SELECT * FROM ( SELECT BATCH_ID ,CONTACT_ID ,COL_VALUE ,TABLE_SRC FROM MISMATCH ) PIVOT ( max(COL_VALUE) FOR TABLE_SRC IN ('1BASE' BASE, '1INT' INT) )
Advertisement
Answer
You can include LVL
in the inner sub-query:
SELECT * FROM ( SELECT BATCH_ID ,CONTACT_ID ,COL_VALUE ,TABLE_SRC ,LVL FROM MISMATCH ) PIVOT ( max(COL_VALUE) FOR TABLE_SRC IN ('1BASE' BASE, '1INT' INT) )
If you don’t want it in the output then change from SELECT *
to a list of columns.
Which, for your sample data:
CREATE TABLE mismatch (batch_id, contact_id, LVL, COL_VALUE, TABLE_SRC) AS SELECT 1, 1, 16, 'INT: ADDRESS_LINE_2:NULL', '1INT' FROM DUAL UNION ALL SELECT 1, 1, 16, 'BASE: ADDRESS_LINE_2:X', '1BASE' FROM DUAL UNION ALL SELECT 1, 1, 17, 'INT: ADDRESS_LINE_3:NULL', '1INT' FROM DUAL UNION ALL SELECT 1, 1, 17, 'BASE: ADDRESS_LINE_3:X', '1BASE' FROM DUAL;
Outputs:
BATCH_ID CONTACT_ID LVL BASE INT 1 1 16 BASE: ADDRESS_LINE_2:X INT: ADDRESS_LINE_2:NULL 1 1 17 BASE: ADDRESS_LINE_3:X INT: ADDRESS_LINE_3:NULL
db<>fiddle here