Services

Technologies

Industries

About Us

Our Work - Case Studies

PIVOT function in SQL

How to use the PIVOT function in SQL

Introduction to the PIVOT function in SQL

The PIVOT function in SQL can be used to create a dataset with aggregated rows from a list of data

This post shows you how to transform a table of data like this

A table o f dates

Into a summary table of data with a column representing each month

A PIVOT summary of data in SQL

If you are familiar with Pivot tables in Excel you will notice that this looks similar

Creating a PIVOT table in SQL can be very useful if you need to work with a summary of data and do comparisons across columns

How to create a PIVOT table in SQL

(SQL Pivoting)

In our example we will start with the simple list of dates

select year, MonthName, date

from [dbo].[DimDate]

where year in (2018, 2019, 2020, 2021, 2022)

A table o f dates

(In reality you wouldn’t use a list of dates, but in this example it makes it simple to see how it works)

Syntax of the SQL PIVOT function

select * from

(

select year, MonthName, date from [dbo].[DimDate]

where year in (2018, 2019, 2020, 2021, 2022)

) a

PIVOT

(count(date)

for Monthname in

(January, February, March, April, May, June, July, August, September, October, November, December)

) b

Breaking this down,

PIVOT

(count(date) –> this sets the aggregation to be a simple count of dates in our SQL PIVOT table

for Monthname in

(January, February, March, April, May, June, July, August, September, October, November, December)  –> this defines the values to analyse, these are explicitly defined. We do not not have to return a column for each month, and we can set the order as we choose

Use cases for SQL Pivoting

SQL Pivot is a great solution when you need to be able to see data over a range of columns, giving you the ability to transform data orientation from Portrait to landscape

A common example we use often is when analysing seasonality

Analysis of seasonality is based around having a fixed number of data points within a year so lends itself well to using this method. It could also be used to capture sales of a product category over weeks of the year, seeing seasonal increases and decreases over a range of years

Average Daily temperature by year

SQL pivoting is worth spending the time learn, bringing the power to transform data in summary tables to make analysis easier

Watch the video

You may be interested in How to create Pivot Tables in Excel

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert