This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Pivoting Data in SQL Server Part two of a three-part series of blogs
Using the Pivot operator in SQL Server allows you to create basic pivot tables from your query results. This blog teaches you how to create simple, static pivots, crosstab-style tables and exciting, dynamic pivot tables. Pivoting Data in SQL Server Using the Pivot Operator in SQL Server (this blog) Dynamic Pivot Tables

Pivoting Data in SQL Server

Part two of a three-part series of blogs

Using the Pivot operator in SQL Server allows you to create basic pivot tables from your query results. This blog teaches you how to create simple, static pivots, crosstab-style tables and exciting, dynamic pivot tables.

Pivoting Data in SQL Server
Using the Pivot Operator in SQL Server (this blog)
Dynamic Pivot Tables

Posted by Andrew Gould on 09 June 2014

Using the Pivot Operator in SQL Server

There are several stages involved in applying the PIVOT operator to create a pivot table. This part of the article leads you step-by-step through the process of creating the pivot table shown below:

This is the simple example we'll create.

Selecting the Base Data

For this example we want to create a pivot table of our movie data where the country names form the column headings. We'll start by creating a simple query to select the base data:

SELECT

CountryName

,FilmID

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

Creating a Temporary Data Set

In order to pivot the data we need to create some form of temporary data set which we can pivot. You can use Common Table Expressions (CTE), Derived Tables or Temporary Tables to do this.

To use a CTE:

--create a CTE

WITH BaseData AS

(

SELECT

CountryName

,FilmID

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

)

--Select everything from the CTE

SELECT * FROM BaseData

--pivot the data

To use a derived table:

--Select everything

SELECT * FROM

(

--from this derived table

SELECT

CountryName

,FilmID

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

) AS BaseData

--pivot the data

To use a temporary table:

--Select the base data

SELECT

CountryName

,FilmID

--into a temporary table

INTO #BaseData

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

--Select everything from the temp table

SELECT * FROM #BaseData

--pivot the data

--delete the temp table

DROP TABLE #BaseData

The result of running each of the queries shown above is a simple table of data containing two columns:

The next step is to pivot the results so that the country names become column headings.

The Pivot Operator

After you've selected the data from your temporary data set you can apply the Pivot operator. The basic syntax is shown below:

PIVOT

(

AggregateFunction([Field to aggregate])

FOR [Field to pivot]

IN ([Column name 1], [Column name 2], [Column name n])

) AS PivotTableAlias

In our example using a CTE the code would look like this:

--create a CTE

WITH BaseData AS

(

SELECT

CountryName

,FilmID

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

)

--Select everything from the CTE

SELECT * FROM BaseData

--pivot the data

PIVOT

(

COUNT(FilmID)

--pivot this column

FOR CountryName

--convert these values into column names

IN (

[China]

,[France]

,[Germany]

,[Japan]

,[New Zealand]

,[Russia]

,[United Kingdom]

,[United States]

)

) AS PivotTable

The results are shown below:

The values from the CountryName column have become column headings in the pivoted data.

Including Row Groups

Including row groups in the final pivot table is as simple as selecting extra columns in the query which generates the base data. For example, if we wanted row groups containing the year of each film's release:

--create a CTE

WITH BaseData AS

(

SELECT

CountryName

--Include this extra column

,YEAR(FilmReleaseDate) AS [FilmYear]

,FilmID

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

)

--Select everything from the CTE

SELECT * FROM BaseData

--Pivot the data

PIVOT

(

COUNT(FilmID)

--Pivot on this column

FOR CountryName

--Convert these values into column names

IN (

[China]

,[France]

,[Germany]

,[Japan]

,[New Zealand]

,[Russia]

,[United Kingdom]

,[United States]

)

) AS PivotTable

--Optionally, sort the results

ORDER BY

FilmYear DESC

You don't need to alter the Pivot clause at all - any columns in the temporary data set which aren't pivoted will automatically generate row groups in the final output:

Simply including the extra column in the original data set is enough to generate the row groups.

What's Next?

Creating a static pivot table such as the ones shown above is relatively straightforward. Dynamic pivot tables are arguably more useful, however, and the next part of this article explains how to create them. Don't forget that we offer a full range of training in SQL (not just in pivoting data!).

Parts of this blog
Pivoting Data in SQL Server Using the Pivot Operator in SQL Server (this blog) Dynamic Pivot Tables

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using the Pivot Operator in SQL Server

Selecting the Base Data

Creating a Temporary Data Set

The Pivot Operator

Including Row Groups

What's Next?

Head office

London

Manchester