Introduction
MSDN states that you cannot pivot two columns, this is correct. If you want two columns with the same column header, then you cannot achieve it short of a manual massage or a reporting tool.
However, you can pivot two or more columns with a little organization. In this mini article, I will show how to pivot two fields. From the Adventureworks database, I will display the number of male and female hires per year per department, as in the above image.
Background
A developer often runs across a requirement that the tool, be it a language, or SQL or third party tool, is not designed to accomplish. In my opinion, the skill of the developer is in bending the environment to meet the requirements. Sometimes, this is not possible, but a good developer should be able to achieve most business requirements.
Most examples of the pivot use static column labels; this is rarely, if ever a real world solution. In every single case where I have used a pivot to solve a requirement, I have had to use dynamic column headers. This requires you to use dynamic SQL, the bane of all developers' lives.
Normally, I would use a table variable to prepare my data; however, you cannot pass a table variable to dynamic SQL. You can, however, reference a temp table, and that is what has been used in this example.
Some of the SQL demonstrated in this snippet:
- Using a temporary table to get a result set into dynamic SQL
- Cleaning up temp tables
- Table variables
Case
statement to do conditional aggregation- Dynamic SQL
- Concatenation of rows in a single
Select
statement - How to construct a pivot select
Using the code
You need to have the Adventureworks sample database installed. The download includes three files pivot01/2/3. These demonstrate the stages of the development of the snippet.
When constructing a pivot statement, you need three types of information in your table. The row axis, in this case the department labels, the column axis, the year/gender labels, and the data to populate the body of the pivot, in this case, the count of male/females by their hire year.
Preparing the data: We need to get a table with the following results. This is achieved with Pivot01.sql. Note the use of the CASE
statement to aggregate only a single gender in each of the union selects.
Also, we need to differentiate the column labels for each year/gender.
CREATE TABLE #TblX(Department VARCHAR(100),Staff INT, Yr VARCHAR(10))
INSERT [#TblX]
SELECT
D.Department,
SUM(CASE E.Gender WHEN 'M' THEN 1 ELSE 0 END) Staff,
CONVERT(CHAR(4),DATEPART(yy,E.HireDate)) + '-M' Yr
FROM HumanResources.Employee E
INNER JOIN HumanResources.vEmployeeDepartment D ON D.EmployeeID = E.EmployeeID
GROUP BY
D.Department,
DATEPART(yy,E.HireDate)
UNION
SELECT
D.Department,
SUM(CASE E.Gender WHEN 'F' THEN 1 ELSE 0 END) Staff,
CONVERT(CHAR(4),DATEPART(yy,E.HireDate)) + '-F' Yr
FROM HumanResources.Employee E
INNER JOIN HumanResources.vEmployeeDepartment D ON D.EmployeeID = E.EmployeeID
GROUP BY
D.Department,
DATEPART(yy,E.HireDate)
Organizing the column labels
This is done in Pivot02.SQL. What we need is a CSV string of all the labels (year/gender) to be pivoted. This is one of the reasons I use a table variable, I do not need to go back to the disk again to get the column labels.
Because I cannot use Select Distinct
and the string concatenation, I have split it into two operations and used another table variable to get the distinct year/gender column labels.
DECLARE @TblY TABLE(Yr CHAR(6))
INSERT @TblY
SELECT DISTINCT Yr FROM [#TblX]
SELECT @List = ISNULL(@List,'') + CASE
WHEN ISNULL(@List,'') = '' THEN '[' + yr + ']'
ELSE ',[' + Yr + ']' end
FROM @TblY
ORDER BY Yr
We now have all the bits ready to put together into a pivot select.
So, the inner query which will supply the data is:
Select Department, Yr, Staff FROM #TblX F
This we need to wrap with the start and end parts of the pivot. The start supplies the column information, so it is Department
and @List
. The end supplies the aggregation and the pivot. In this case, the aggregation has already be done, so we simply use MAX()
to display the staff count, tell it the column to pivot, and supply the column values - @List
.
Set @SQL = 'Select Department, ' + @List + char(13)
Set @SQL = @SQL + 'From (Select Department, Yr, Staff ' + char(13)
Set @SQL = @SQL + 'FROM #TblX F) P ' + char(13)
Set @SQL = @SQL + 'Pivot (Max(Staff) For Yr In (' + @List + ')) as Pvt' + char(13)
Print @SQL
Exec (@SQL)
DROP TABLE [#TblX]
Points of interest
I hate the gotcha where you cannot use a variable in dynamic SQL. I find the concatenation technique endlessly useful. I just wish I knew who originated it to thank them.
Adding additional columns to be pivoted is simply a matter of organizing the labels and rows in the temp table. E.g., you could go to sales, and pivot sales/target/performance for each sales person per year.
Of course, the business is going to come back and complain that they do not like the NULL
s and want them replaced by 0. This can be achieved by a cross join of year/gender and departments and have your initial preparation select update the temp table instead of inserting.
History
Cleaned up some of the formatting.
Started my programming life writing Excel 1.0 macros, God what a long time ago.
Now I'm a dotnet developer, I get to influence direction, play with new toys, build stuff, life is wonderful.
Greatest buzz you can get, walk past a row of desks and see your application running on all of them (and getting paid).
Greatest irritant, pouring 12 months of knowledge and experience into an empty head only to have it leave.
And now I'm retired, no deadlines, no meetings, no managers (except the ONE) and no users!