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.

  1. Pivoting Data in SQL Server
  2. Using the Pivot Operator in SQL Server (this blog)
  3. Dynamic Pivot Tables

Posted by Andrew Gould on 09 June 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

A simple pivot table

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:

Base data

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:

Results of basic pivot

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:

Row and column groups

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.

  1. Pivoting Data in SQL Server
  2. Using the Pivot Operator in SQL Server (this blog)
  3. Dynamic Pivot Tables
This blog has 0 threads Add post