560 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 three 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.
|
Perhaps the biggest limitation of the PIVOT operator is that you must explicitly list each column name that you want to create. Not only is this is immensely tedious when you have a large number of columns, you may not actually know what the column names will be. The solution is to create a dynamic pivot table using dynamic SQL.
Start by creating a simple query to display all of the values that you want to become column headings in the final pivot table. In our example we'll select all of the country names using this statement:
SELECT CountryName
FROM tblCountry
The result of the query is shown below:
Your query may be more complex than the one shown above: the aim is to create a list of all column names for the final pivot table.
The next step is to create a comma-separated string of the values from the query results. The code below shows how to do this:
--A variable to hold the complete string
DECLARE @Columns VARCHAR(MAX) = ''
--Concatenate each country with a comma
SELECT @Columns += (CountryName + ',')
FROM tblCountry
--Check the result
PRINT @Columns
The result of executing this code is shown below:
The result is a comma-separated list of country names.
Each column name needs to be enclosed in a set of square brackets. We can achieve this by using the QUOTENAME function:
--A variable to hold the complete string
DECLARE @Columns VARCHAR(MAX) = ''
--Concatenate each country with a comma
--The QUOTENAME function adds square brackets around each value
SELECT @Columns += (QUOTENAME(CountryName) + ',')
FROM tblCountry
--Check the result
PRINT @Columns
The new result of executing the code looks like this:
Each country name is enclosed in square brackets.
At this point our list of column names contains an extra comma at the end of the string. One way to remove this is shown below:
--A variable to hold the complete string
DECLARE @Columns VARCHAR(MAX) = ''
--Concatenate each country with a comma
--The QUOTENAME function adds square brackets around each value
SELECT @Columns += (QUOTENAME(CountryName) + ',')
FROM tblCountry
--Remove the trailing comma
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)
--Check the result
PRINT @Columns
The LEN function calculates the number of characters in the string. We subtract 1 from the result and use the LEFT function to return that number of characters from the left of the string.
The next step is to generate the complete SQL statement that will create the pivot table and store the result in a string variable.
--A variable to hold the SQL string
DECLARE @SQL NVARCHAR(MAX) = ''
--Store the complete string in the variable
--Concatenate the list of column names
SET @SQL =
'WITH BaseData AS
(
SELECT
CountryName
,FilmID
FROM
tblFilm AS f INNER JOIN
tblCountry AS c
ON c.CountryID=f.FilmCountryID
)
SELECT * FROM BaseData
PIVOT
(
COUNT(FilmID)
FOR CountryName IN (' + @Columns + ')
) AS PivotTable'
--Check the result
PRINT @SQL
Printing the result is a good way to check that you've generated a valid SQL statement:
The complete SQL statement.
The final step is to execute the dynamic SQL statement using either of the two techniques shown below:
--Execute the statement directly
EXECUTE (@SQL)
--Use a system stored procedure
EXECUTE sp_executesql @SQL
The final result should look like this:
A simple, dynamic pivot table.
From this point you can easily create more complex pivot tables by adding code to the dynamic SQL statement.
If you've enjoyed reading this blog, have a look at our SQL training page for much more information on how you can learn to write better SQL.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
From: | inventif |
When: | 25 Feb 17 at 16:07 |
This is absolutely top-notch. Not only knowledgeable, but explained systematically and with a clear pedagogical flavour. Thank you for posting it!
One small addition would be helpful: how do you deal with a situation where the values to be pivoted into column headers come from a non-distinct column and would need to be made distinct before using them as column/field names?
From: | Andrew G |
When: | 27 Feb 17 at 06:53 |
Thanks! Happy to hear that you found it useful. If you need to generate a list of distinct values to create the pivot column headers you could use a Select Distinct statement. For the example in this article you could say:
SELECT DISTINCT
CountryName
FROM
tblCountry
I hope that helps!
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.