PIVOT-UNPIVOT

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)

UNPIVOT

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

Example:

So, a table that looks like this:

Can be converted into something like this:

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;

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