Pivots - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Pivots

Author

Topic

andypgill
Starting Member

34 Posts

Posted - 2013-02-01 : 10:48:53

Hi All

Can anyone help me with my code.

I'm trying to create a pivot.

What I want it to do is display the filed [POD Codes ABC] and place the count of them in the relevant period column.

This is my code.

SELECT [POD Codes ABC],
(case [MONTH] when '2012-04' then
COUNT ([POD Codes ABC]) else 0 end ) M4,
(case [MONTH] when '2012-05' then
COUNT ([POD Codes ABC]) else 0 end ) M5,
(case [MONTH] when '2012-06' then
COUNT ([POD Codes ABC]) else 0 end ) M6,
(case [MONTH] when '2012-07' then
COUNT ([POD Codes ABC]) else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC],[month]

This gives me

POD Codes ABC M4 M5 M6 M7
HAA3040D 343 0 0 0
HAA3040D 0 353 0 0
HAA3040D 0 0 86 0
HAA3040D 0 0 0 1

How do I write the code so it gives me one line per [POD Codes ABC]

eg
POD Codes ABC M4 M5 M6 M7
HAA3040D 343 353 86 1

Thanks

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-02-01 : 11:57:11

Hi

Sorry have to rush off but here is a few links to questions I had on here for pivots, might/might not be relevant but hope it helps:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164981&SearchTerms=pivot

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165475&SearchTerms=pivot

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175001&SearchTerms=pivot

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-01 : 17:31:33

Move the COUNT function to outside the CASE expression like this:

SELECT [POD Codes ABC], 
COUNT (case [MONTH] when '2012-04' then 
[POD Codes ABC] else 0 end ) M4,
....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-02 : 03:14:30

should be


SELECT [POD Codes ABC], 
SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,
SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,
SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,
SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC]

if you're looking only for a particular code like HAA3040D then no need of GROUP BY

just do


SELECT 
SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,
SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,
SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,
SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-02 : 15:40:09

Thanks Visakh, for that.

To add some color to the change that Visakh made:


-- This would return the same count regardless of whether MONTH is 2012-04 or not, 
-- because count is simply counting the NON-NULLS
COUNT (case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4

-- You can make that work using COUNT if you remove the else clause
COUNT (case [MONTH] when '2012-04' then [POD Codes ABC] end ) M4

-- But that has the often annoying message that says
-- Warning: Null value is eliminated by an aggregate or other SET operation.
-- using the SUM as shown in Visakh's query eliminates that annoyance
SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4

andypgill
Starting Member

34 Posts

Posted - 2013-02-04 : 05:39:55

quote:

Originally posted by visakh16

should be


SELECT [POD Codes ABC], 
SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,
SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,
SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,
SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC]

if you're looking only for a particular code like HAA3040D then no need of GROUP BY

just do


SELECT 
SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,
SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,
SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,
SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the reply however I get an error "Conversion failed when converting the nvarchar value 'HAA3040D' to data type int."

Should I be using sum as I want to count the number of times for each [POD Codes ABC], which in this example is 'HAA3040D'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 05:42:38

Is [pod codes abc] integer type? May be this?

SELECT [POD Codes ABC], 
SUM(case [MONTH] when '2012-04' then 1 else 0 end ) M4,
SUM(case [MONTH] when '2012-05' then 1 else 0 end ) M5,
SUM(case [MONTH] when '2012-06' then 1 else 0 end ) M6,
SUM(case [MONTH] when '2012-07' then 1 else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC]

andypgill
Starting Member

34 Posts

Posted - 2013-02-04 : 05:48:37

quote:

Originally posted by James K

Is [pod codes abc] integer type? May be this?

SELECT [POD Codes ABC], 
SUM(case [MONTH] when '2012-04' then 1 else 0 end ) M4,
SUM(case [MONTH] when '2012-05' then 1 else 0 end ) M5,
SUM(case [MONTH] when '2012-06' then 1 else 0 end ) M6,
SUM(case [MONTH] when '2012-07' then 1 else 0 end ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC]

Brilliant, thanks for your help.

Out of interest [pod codes abc] is nvarchar(255)

Andy

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 05:55:12

Now that I think about it, when you change your query from using COUNT to using a SUM, you have to use 1 instead of the column even if the data type is integer or other numeric type. My mistake! Sorry about that, but glad it worked out for you in the end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources