562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
|
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.
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
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.
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 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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.