Oracle Unpivot Multiple Columns into Multiple Columns

Unpivoting tables is a powerful technique in SQL that allows you to transform rows into columns. In this article, we will explore the use of Oracle’s UNPIVOT clause to unpivot multiple columns into separate columns.

Introduction

The UNPIVOT clause in Oracle is used to transform rows into columns. When using UNPIVOT, you need to specify the columns that you want to unpivot and the values that will be used for these new columns. In this article, we will focus on how to use the UNPIVOT clause with multiple columns.

Background

Before diving into the code, let’s understand the basics of UNPIVOT. The UNPIVOT clause is used in conjunction with the PIVOT clause. When you use PIVOT, you are grouping data by one or more columns and then aggregating other values based on those groups. However, when using UNPIVOT, you are essentially reversing this process, transforming rows into columns.

Sample Data

Let’s start with a sample table that we will unpivot:

+----+-----+-----+
| ID | COLA | COLB |
+----+-----+-----+
| 1  | a    | b    |
| 2  | c    | d    |
+----+-----+-----+

This table has two columns (COLA and COLB) and two rows. We want to unpivot this table into separate columns, where each column represents one of the original columns.

Using UNPIVOT

To use the UNPIVOT clause with multiple columns, we need to specify these columns in the UNPIVOT clause. The syntax for UNPIVOT is as follows:

SELECT *
FROM (
  SELECT ID,
         'COLA' AS COL,
         cola AS dataa
  FROM your_table
  UNION ALL
  SELECT ID,
         'COLB' AS COL,
         colb AS dataa
)
UNPIVOT (
  (dataa FOR col IN ('COLA', 'COLB'))
) AS unpivot_table;

In this example, we are using a UNION operator to combine two separate queries. The first query selects the ID, cola values and labels them as 'COLA'. Similarly, the second query selects the ID, colb values and labels them as 'COLB'. We then use the UNPIVOT clause to transform these rows into columns.

Note that in our example, we are using two separate queries with UNION ALL to combine the data. However, this can be cumbersome when dealing with many columns. In such cases, it’s better to use a single query with multiple UNION operators.

Alternative Approach

Another approach is to use UNION ALL 10 times to create the desired output:

SELECT ID, 'COLA' AS COL, cola AS dataa FROM your_table
UNION ALL
SELECT ID, 'COLB' AS COL, colb AS dataa FROM your_table
UNION ALL
SELECT ID, 'COLC' AS COL, colc AS dataa FROM your_table
...
UNION ALL
SELECT ID, 'COLJ' AS COL, colj AS dataa FROM your_table;

However, this approach is not recommended as it can be lengthy and difficult to maintain.

Explanation

Let’s take a closer look at how the UNPIVOT clause works:

SELECT *
FROM (
  SELECT ID,
         'COLA' AS COL,
         cola AS dataa
  FROM your_table
)
UNPIVOT (
  (dataa FOR col IN ('COLA'))
) AS unpivot_table;

In this example, we are selecting the ID, 'COLA' values and labeling them as 'COLA'. We then use the UNPIVOT clause to transform these rows into columns. The IN operator specifies that the values in the col column should match the names in the list (('COLA')`.

Performance

When using UNPIVOT, Oracle uses a technique called “pushdown” to optimize performance. This means that instead of pushing the unpivoted columns up to the query optimizer, the database server pushes them down to the row-level optimizer.

SELECT *
FROM (
  SELECT ID,
         'COLA' AS COL,
         cola AS dataa
  FROM your_table
)
UNPIVOT (
  (dataa FOR col IN ('COLA', 'COLB'))
) AS unpivot_table;

In this example, we are specifying multiple columns in the IN list. Oracle uses this information to optimize the query plan.

Conclusion

Unpivoting tables is a powerful technique in SQL that allows you to transform rows into columns. When using the UNPIVOT clause with multiple columns, it’s essential to specify these columns correctly and use the correct syntax. The example provided demonstrates how to unpivot two columns (COLA and COLB) from a sample table.

+----+-----+-----+
| ID | COL  | VALUE |
+----+-----+-----+
| 1  | COLA | a    |
| 1  | COLB | b    |
| 2  | COLA | c    |
| 2  | COLB | d    |
+----+-----+-----+

Last modified on 2024-01-12