Skip to content

SQL Pivot and Unpivot

PIVOT:

The pivot table allows us to display the data in a table horizontally, meaning that the information in the rows is translated into columns according to the fields we want. Specifically, while preparing the report, it allows us to make the data more understandable and regular. For example, we have an order table. Each order a customer gives is shown in one order. We would like to see the last 6 months order details for each customer. The first method may be to write down every move, but it is more understandable, and for every regular report, each customer must be on one line and the sum of all orders for the last six months must be shown in this line. In this case, we use a pivot table.

UNPIVOT:

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOTrotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and runs aggregations where they’re required on any left over column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.


SQL Pivot & Unpivot Examples

SELECT model_year,COUNT(product_name) [Number of models produced]
FROM BikeStores.production.products
GROUP BY model_year

SELECT *
FROM
(
	SELECT pro.model_year FROM BikeStores.production.products as pro
) as tab

PIVOT(
	COUNT(model_year)
	FOR model_year IN ([2016],[2017],[2018],[2019])
) as piv

DECLARE @unpivot table([2016] int,[2017] int,[2018] int,[2019] int)
INSERT INTO @unpivot

SELECT *
FROM
(
	SELECT pro.model_year FROM BikeStores.production.products as pro
) as tab

PIVOT
(
	COUNT(model_year)
	FOR model_year IN ([2016],[2017],[2018],[2019])
) as piv

SELECT * FROM
(
	SELECT [2016],[2017],[2018],[2019]
	FROM @unpivot
) as tab

UNPIVOT
(
	Model_count
	FOR model_year
	IN ([2016],[2017],[2018],[2019])
) as unpiv

To access the database: click

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: