DEV Community

loading...

PIVOT-UNPIVOT

Soumitra Banerjee
Data Engineer
・1 min read

What is PIVOT?

You can rotate your SQL table using pivot function where you transform your row values into columns. Here you can turn unique values from one column into multiple columns.

What is UNPIVOT?

While PIVOT turns values from one column into multiple columns, UNPIVOT turns multiple column into row values of single column.

Use Cases

These two functions of SQL is generally used to reorganize data, so that, the data can be viewed more efficiently and is more understandable than before.

Syntax:

PIVOT

SELECT (Column1, Column2, ...) 
FROM (TableName) 
PIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn 
   IN (PivotColumnValues1, PivotColumnValues2, ...)
 ) AS (Alias)
Enter fullscreen mode Exit fullscreen mode

UNPIVOT

SELECT (Column1, Column2, ...) 
FROM (TableName) 
UNPIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn 
   IN (PivotColumnValues1, PivotColumnValues2, ...)
 ) AS (Alias)
Enter fullscreen mode Exit fullscreen mode

Example:

So, a table that looks like this:
image

Can be converted into something like this:
image

With this piece of code:

SELECT BRAND, TABLET, MOBILE, OS 
FROM(
    SELECT BRAND, PRODUCT, PRICE FROM TECHPRODUCTS
)
PIVOT (
    SUM(PRICE)
    FOR (product)
    IN ('TABLET' TABLET, 'MOBILE' MOBILE, 'OS' OS)
) ORDER BY BRAND;
Enter fullscreen mode Exit fullscreen mode

If you want to learn more about PIVOT and UNPIVOT please refer:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

Discussion (0)