Pivots vs Crosstabs when counting 0 values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivots vs Crosstabs when counting 0 values

Author

Topic

jrunowski
Starting Member

8 Posts

Posted - 2010-08-20 : 10:15:42

Hello,
I almost 100% sure this is a issue with the programmer (me) but i would still like to get some other thoughts on this. I'm writing a pretty simple crosstab report that performs a count on a column called CustomerResponse which has 4 possible values; 0-3. There is also non-pivoted column called Appliction which shows the name of the application in which the customer responded to. When using the MS SQL 2005 pivot method the count for column 0 (meaning no response) shows a total of 0 for all applications; this is incorrect. I can run a simple select count(*) statement to verify that there is infact customer responses of 0 for some of applications. If switch to the MS SQL 2000 method of using case statements it works perfect. Is it possible that the PIVOT command cant pivot with an aggreate of count on values equal to 0 or is there an issue with my code? I would appreciate any help on this issue/topic? Thanks

Not working properly PIVOT method:


SELECT Category as 'Application',0 AS 'No Response',[1] AS 'Yes',[2] AS 'No'
,[3] AS 'Callback Requested'
FROM 
(select category, customerResponse
from HTCustomerSurveys
) p
PIVOT
(
COUNT (customerResponse)
FOR customerResponse IN
( [0],[1],[2],[3])
) AS pvt
ORDER BY Category

Working CASE method:


SELECT category,
 SUM(CASE customerResponse WHEN '0' THEN 1 ELSE 0 END) AS 'No Response',
 SUM(CASE customerResponse WHEN '1' THEN 1 ELSE 0 END) AS 'Yes',
 SUM(CASE customerResponse WHEN '2' THEN 1 ELSE 0 END) AS 'No',
 SUM(CASE customerResponse WHEN '3' THEN 1 ELSE 0 END) AS 'Callback Requested'
 FROM HTCustomerSurveys
GROUP BY category
order by category

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 10:19:24

0 AS 'No Response'

should be

[0] AS 'No Response'

Madhivanan

Failing to plan is Planning to fail