In some situations there may be a reason to total data alongside a column you want to pivot. In this example we will get a grand total of saverPoints that each salesperson earns with their sales as well as separating the number of sales by month.
create table #tmpPivotTest
(salesPersonsName varchar(25),
numberOfSales int,
monthAbv varchar(3),
saverPoints int)
insert into #tmpPivotTest values ('Yagnesh', 1, 'Jan', 1)
insert into #tmpPivotTest values ('Yagnesh', 2, 'Feb', 2)
insert into #tmpPivotTest values ('Yagnesh', 2, 'Feb', 2)
insert into #tmpPivotTest values ('Yagnesh', 2, 'Jan', 2)
insert into #tmpPivotTest values ('Jason', 3, 'Feb', 2)
insert into #tmpPivotTest values ('Jason', 1, 'Feb', 1)
--Shows how many sales each sales person gets per month
select salesPersonsName, [Jan], [Feb] from
(select salesPersonsName, numberOfSales, monthAbv from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as PivotTable
salesPersonsName Jan Feb
------------------------- ----------- -----------
Jason NULL 4
Yagnesh 3 4
This basic pivot works flawlessly. When we try to add another column to the select we don't get everything.
--Try to get the saver points as well
select salesPersonsName, [Jan], [Feb], saverPoints from
(select * from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable
salesPersonsName Jan Feb saverPoints
------------------------- ----------- ----------- -----------
Jason NULL 1 1
Yagnesh 1 NULL 1
Jason NULL 3 2
Yagnesh 2 4 2
We are missing some of the saver points in here and it splits the sales person with each distinct value of saverPoints. For some reason pivot isn't working correctly! It's actually losing data! Lets try to sum the values together again...
select salesPersonsName, sum([Jan]) as Jan, sum([Feb]) as Feb, sum(saverPoints) as saverPoints from
(select * from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable
group by salesPersonsName
select salesPersonsName, sum(saverPoints) as saverPoints from #tmpPivotTest group by salesPersonsName
salesPersonsName Jan Feb saverPoints
------------------------- ----------- ----------- -----------
Jason NULL 4 3
Yagnesh 3 4 3
salesPersonsName saverPoints
------------------------- -----------
Jason 3
Yagnesh 7
The saver points are still missing. The saver points are ignored since they are the same on both rows. If we change the rows ever so slightly we can get the correct answer.
select salesPersonsName, sum([Jan]) as Jan, sum([Feb]) as Feb, sum(saverPoints) as saverPoints from
(select *, newid() as superDecombobulatorV2
from #tmpPivotTest) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable
group by salesPersonsName
select salesPersonsName, sum(saverPoints) as saverPoints from #tmpPivotTest group by salesPersonsName
salesPersonsName Jan Feb saverPoints
------------------------- ----------- ----------- -----------
Jason NULL 4 3
Yagnesh 3 4 7
salesPersonsName saverPoints
------------------------- -----------
Jason 3
Yagnesh 7
After being frustrated with pivot not working "correctly" and finally looking at books online with a cool head. We find that it is working according to the documentation in books online. From books online:
The following is annotated syntax for PIVOT.
SELECT <non-pivoted column> ,
[first pivoted column] AS <column name> ,
[second pivoted column] AS <column name> ,
...
[last pivoted column] AS <column name>
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
PIVOT
(
<aggregation function>( <column being aggregated> )
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column] , [second pivoted column] ,
... [last pivoted column] )
) AS <alias for the pivot table>
<optional ORDER BY clause>
Notice it only has one non-pivoted column and each value of the pivoted column listed. If I'd of had been level headed instead of trying to force pivot to work I would have just done two queries. One to get the pivoted data into a temp table, and the other to get the summed data and update the temp table. So let the lesson be learned. You can sometimes force your way through "incorrect" things in SQL Server, but it may just be easier to calm down and read the documentation.
PS: Don't forget to clean up
drop table #tmpPivotTest
