Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PIVOT working incorrectly?

By Ryan Clare,

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
Total article views: 7527 | Views in the last 30 days: 3
 
Related Articles
FORUM

Pivot

Pivot

FORUM

PIVOT FOR 2 or more Columns

Want 2 columns in PIVOT

FORUM

Pivot with dynamic number of columns

Pivot with dynamic number of columns

FORUM

Pivot Query- need help

Pivot

FORUM

PIVOT working incorrectly?

Comments posted to this topic are about the item [B]PIVOT working incorrectly?[/B] Interesting, than...

Tags
pivot    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones