Introduction

This article comes as a continuation to the article on PIVOT posted on 9th Of may 2026 which is also a part of my series o articles on greta Oracle features which are unjustly ignored or blatantly unused.

As we know, Oracle Database provides powerful SQL features for transforming data between different structures.

One of the most useful transformations is converting columns into rows, commonly known as unpivoting data.

This article explains how to convert columns to rows in Oracle Database using the UNPIVOT operator, along with practical examples, syntax explanations, performance considerations, and best practices.

In relational databases, data is often stored in a wide format, where multiple attributes are represented as separate columns. For example:

Sometimes applications, reporting tools, or analytics require the data in a normalized row format. E.g.:

Oracle Database provides the UNPIVOT operator specifically for this purpose.

What is UNPIVOT in Oracle Database?

The UNPIVOT operator transforms columns into rows.

It is the reverse operation of the PIVOT operator.

Key Benefits

Simplifies transformation logic

Eliminates complex UNION ALL statements

Improves readability

Useful for:

• Reporting
• ETL processes
• Data warehousing
• Analytics
• Data normalization

Basic UNPIVOT Syntax

SELECT *
FROM table_name
UNPIVOT (
    value_column
    FOR name_column
    IN (
        column1,
        column2,
        column3
    )
);

Example 1: Convert Quarterly Sales Columns to Rows

Create Sample Table

CREATE TABLE sales_data (
    employee_id NUMBER,
    q1_sales NUMBER,
    q2_sales NUMBER,
    q3_sales NUMBER,
    q4_sales NUMBER
);

Insert Sample Data

INSERT INTO sales_data VALUES (101, 10000, 12000, 15000, 13000);
INSERT INTO sales_data VALUES (102, 9000, 11000, 14000, 12500);
COMMIT;

Using UNPIVOT

SELECT employee_id,
       quarter,
       sales
FROM sales_data
UNPIVOT (
    sales
    FOR quarter IN (
        q1_sales,
        q2_sales,
        q3_sales,
        q4_sales
    )
);

The output changes as below:

EMPLOYEE_ID   Q1_SALES   Q2_SALES   Q3_SALES   Q4_SALES
----------- ---------- ---------- ---------- ----------
        101      10000      12000      15000      13000
        102       9000      11000      14000      12500
EMPLOYEE_ID QUARTER       SALES
----------- -------- ----------
        101 Q1_SALES      10000
        101 Q2_SALES      12000
        101 Q3_SALES      15000
        101 Q4_SALES      13000
        102 Q1_SALES       9000
        102 Q2_SALES      11000
        102 Q3_SALES      14000
        102 Q4_SALES      12500
8 rows selected.

Using Aliases in UNPIVOT

ou can provide meaningful labels for the generated rows.

SELECT employee_id,
       quarter,
       sales
FROM sales_data
UNPIVOT (
    sales
    FOR quarter IN (
        q1_sales AS 'Q1',
        q2_sales AS 'Q2',
        q3_sales AS 'Q3',
        q4_sales AS 'Q4'
    )
);

For an Output like:

UNPIVOT INCLUDE NULLS

By default, Oracle excludes NULL values during unpivoting.

Inserting some NULL values in the test table:

INSERT INTO sales_data VALUES (99, 9000, 11000, 14000,NULL);
COMMIT;

Example Table:

NULL values present:

Default Behavior

The data corresponding to the NULL row is omitted, running the same SQL will not generate a row for the data containing NULLs

SELECT *
FROM sales_data
UNPIVOT (
    sales
    FOR quarter IN (
        q1_sales,
        q2_sales,
        q3_sales,
        q4_sales
    )
);

returns:

EMPLOYEE_ID QUARTER       SALES
----------- -------- ----------
        101 Q1_SALES      10000
        101 Q2_SALES      12000
        101 Q3_SALES      15000
        101 Q4_SALES      13000
        102 Q1_SALES       9000
        102 Q2_SALES      11000
        102 Q3_SALES      14000
        102 Q4_SALES      12500
         99 Q1_SALES       9000
         99 Q2_SALES      11000
         99 Q3_SALES      14000
11 rows selected.

To our case, we will not have data for employee with ID 99 for the Q4 sales:

Using INCLUDE NULLS statement

If we want the row for id 99 and Q4 displayed despite the NULLS, we can add the INCLUDE NULLS after UNPIVOT operator in the SQL:

SELECT *
FROM sales_data
UNPIVOT INCLUDE NULLS (
    sales
    FOR quarter IN (
        q1_sales,
        q2_sales,
        q3_sales,
        q4_sales
    )
);

for a resultset of 12 rows:

EMPLOYEE_ID QUARTER       SALES
----------- -------- ----------
        101 Q1_SALES      10000
        101 Q2_SALES      12000
        101 Q3_SALES      15000
        101 Q4_SALES      13000
        102 Q1_SALES       9000
        102 Q2_SALES      11000
        102 Q3_SALES      14000
        102 Q4_SALES      12500
         99 Q1_SALES       9000
         99 Q2_SALES      11000
         99 Q3_SALES      14000
EMPLOYEE_ID QUARTER       SALES
----------- -------- ----------
         99 Q4_SALES           
12 rows selected. 

Now Oracle includes rows with NULL values.

UNPIVOT Multiple Columns

Oracle also supports unpivoting multiple related columns together.

Example Table

CREATE TABLE product_sales (
    product_id NUMBER,
    q1_qty NUMBER,
    q1_amount NUMBER,
    q2_qty NUMBER,
    q2_amount NUMBER
);

Query

SELECT *
FROM product_sales
UNPIVOT (
    (quantity, amount)
    FOR quarter IN (
        (q1_qty, q1_amount) AS 'Q1',
        (q2_qty, q2_amount) AS 'Q2'
    )
);

Insert some data into it:

/INSERT INTO "ADMIN"."PRODUCT_SALES" (PRODUCT_ID, Q1_QTY, Q1_AMOUNT, Q2_QTY, Q2_AMOUNT) VALUES ('1', '10', '100', '2', '200')
INSERT INTO "ADMIN"."PRODUCT_SALES" (PRODUCT_ID, Q1_QTY, Q1_AMOUNT, Q2_QTY, Q2_AMOUNT) VALUES ('10', '100', '1000', '200', '2000')

Now, I can UNPIVOT and get for each product the sales data per quarter:

SELECT *
FROM product_sales
UNPIVOT (
    (quantity, amount)
    FOR quarter IN (
        (q1_qty, q1_amount) AS 'Q1',
        (q2_qty, q2_amount) AS 'Q2'
    )
);

Converting Columns to Rows Without UNPIVOT

Before Oracle introduced UNPIVOT, developers commonly used UNION ALL.

Back to our original example:

SELECT employee_id, 'Q1' quarter, q1_sales sales
FROM sales_data
UNION ALL
SELECT employee_id, 'Q2', q2_sales
FROM sales_data
UNION ALL
SELECT employee_id, 'Q3', q3_sales
FROM sales_data
UNION ALL
SELECT employee_id, 'Q4', q4_sales
FROM sales_data;

Why UNPIVOT is Better

Performance Considerations

Use UNPIVOT for Cleaner SQL

UNPIVOT improves maintainability and readability.

Large Tables

On very large datasets:

• Ensure proper indexing
• Use partitioning if applicable
• Filter rows before unpivoting

Example:

SELECT *
FROM (
    SELECT *
    FROM sales_data
    WHERE employee_id = 101
)
UNPIVOT (
    sales
    FOR quarter IN (
        q1_sales,
        q2_sales,
        q3_sales,
        q4_sales
    )
);

NULL Handling

Remember:

• Default behavior excludes NULLs
• Use INCLUDE NULLS only when necessary

Common Use Cases

Reporting Systems

Transform monthly or quarterly columns into report-friendly rows.

Data Warehousing

Normalize denormalized source data during ETL operations.

Analytics

Many BI tools work better with row-based structures.

Export and API Formatting

APIs often expect normalized datasets.

Difference Between PIVOT and UNPIVOT

Best Practices

Use Meaningful Aliases

q1_sales AS 'Q1'

Improves readability.

Avoid Excessive Columns

Very wide tables can generate massive row expansion.

Filter Early

Apply WHERE conditions before unpivoting large datasets.

Use INCLUDE NULLS Carefully

Only include NULLs when business logic requires them.

Conclusion

The Oracle UNPIVOT operator is a powerful and elegant feature for converting columns into rows.

It simplifies SQL development compared to older UNION ALL approaches and is especially useful in:

• Reporting
• Data transformation
• ETL pipelines
• Analytics

By understanding UNPIVOT, Oracle developers and DBAs can write cleaner, more maintainable, and scalable SQL queries.