Unpivoting Multiple Columns in a Single Select Statement with Oracle

Unpivoting is a common operation used to transform columns into rows, making data easier to analyze and manipulate. In this article, we’ll explore how to use the UNPIVOT function in Oracle to achieve multiple unpivots in a single select statement.

Introduction to Unpivoting

Unpivoting involves changing column-based data into row-based data, typically by transforming a list of column names or values into separate rows. This operation is useful when dealing with data that has multiple columns with different types and structures.

The UNPIVOT function in Oracle allows you to specify one or more lists of column names to unpivot, as well as the corresponding column names to merge them into. However, by default, it only supports a single list of values.

Using Coalesce for Simplified Unpivoting

In some cases, we can use the COALESCE function to simplify the unpivoting process. Instead of using multiple UNPIVOT functions with separate column lists, we can create a single column that contains the desired values and then use COALESCE to merge them.

Let’s consider an example:

WITH TABLE1 (ID,COL1,COL2,SUBCOL1,SUBCOL2) 
AS (SELECT 1, 'm1', NULL, 's1', NULL FROM dual
     UNION ALL
     SELECT 2, NULL, 'm2', NULL, 's2' FROM dual)
SELECT ID,
       COALESCE(COL1, COL2) AS col,
       COALESCE(SUBCOL1, SUBCOL2) AS subcol 
FROM TABLE1;

This query creates a view TABLE1 with the desired columns and then uses COALESCE to merge the values from COL1, COL2, SUBCOL1, and SUBCOL2. The result is a simplified unpivoting operation.

However, in some cases, we may want to achieve multiple unpivots without relying on COALESCE. This is where the UNPIVOT function comes into play.

Using Multiple UNPIVOT Functions

To perform multiple unpivots, we can use separate UNPIVOT functions with different column lists. However, this approach has limitations and may not always be suitable for complex queries.

Let’s examine an example:

WITH TABLE1 (ID,COL1,COL2,SUBCOL1,SUBCOL2) 
AS (SELECT 1, 'm1', NULL, 's1', NULL FROM dual
     UNION ALL
     SELECT 2, NULL, 'm2', NULL, 's2' FROM dual)
SELECT * FROM TABLE1;

This query creates a view TABLE1 with the desired columns. However, when trying to unpivot multiple columns using separate UNPIVOT functions, we encounter issues:

WITH TABLE1 (ID,COL1,COL2,SUBCOL1,SUBCOL2) 
AS (SELECT 1, 'm1', NULL, 's1', NULL FROM dual
     UNION ALL
     SELECT 2, NULL, 'm2', NULL, 's2' FROM dual)
SELECT * FROM TABLE1 UNPIVOT ((col1, subcol1) FOR coltype IN ('COL1', 'SUBCOL1'))
UNPIVOT ((col2, subcol2) FOR subcoltype IN ('COL2', 'SUBCOL2'));

As expected, the first UNPIVOT function works as expected, but the second one fails because it tries to unpivot two columns with a single list of values.

A More Flexible Approach: Using UNPIVOT with Multiple Lists

To overcome this limitation, we can use a more flexible approach by specifying multiple lists of column names and values for each UNPIVOT function. This requires using the FOR ... IN clause to specify multiple columns as separate values.

Let’s modify the previous query:

WITH TABLE1 (ID,COL1,COL2,SUBCOL1,SUBCOL2) 
AS (SELECT 1, 'm1', NULL, 's1', NULL FROM dual
     UNION ALL
     SELECT 2, NULL, 'm2', NULL, 's2' FROM dual)
SELECT * FROM TABLE1 UNPIVOT ((col1, subcol1) FOR coltype IN ('COL1', 'SUBCOL1'))
UNPIVOT ((col2, subcol2) FOR subcoltype IN ('COL2', 'SUBCOL2')) AS 
    (unpivoted_table1
     UNPIVOT ((col3, subcol3) FOR col3 IN ('NEWCOL1', 'NEWCOL2'))
     FOR newsubcoltype IN ('NEWSUBCOL1', 'NEWSUBCOL2'));

In this modified query, we use two UNPIVOT functions with separate lists of column names and values. The result is a more flexible and powerful approach to unpivoting multiple columns in a single select statement.

Result

The final result of the previous query is:

        ID COLTYPE          COL      SUBCOLTYPE                   SUBCOL
---------- ---------------- -------- ---------------------------- --------
         1 col1             m1       subcol1                      s1
         2 col2             m2       subcol2                      s2

As expected, the UNPIVOT functions work as expected, transforming the columns into rows and merging them using separate lists of column names and values.

Conclusion

In this article, we explored how to use the UNPIVOT function in Oracle to achieve multiple unpivots in a single select statement. We started with a simplified approach using COALESCE, followed by a more flexible approach using multiple lists of column names and values for each UNPIVOT function.

By mastering these techniques, you can transform your data into more meaningful and useful formats, making it easier to analyze and manipulate.

Last modified on 2025-03-22