Search code examples
sqloracle-databasepivottranspose

converting rows to columns using oracle sql


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)
    )

Solution

  • 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