This page has 0 threads | Add post

Using the Pivot Operator in SQL Server

Written by Andrew Gould

In this tutorial

Selecting the Base Data
Creating a Temporary Data Set
The Pivot Operator
Including Row Groups
What's Next?

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!).

Previous
tutorial

Table-valued functions

Previous
part

Pivoting Data in SQL Server

Top of page

Next
part

Dynamic Pivot Tables

Next
tutorial

Triggers

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Pivoting data

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