BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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!).
- Pivoting Data in SQL Server
- Using the Pivot Operator in SQL Server (this blog)
- Dynamic Pivot Tables