oraclepivot

Oracle pivoting for text issue


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


Solution

  • 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

    fiddle