Building Pivot from aggregate summary query

Question

Building Pivot from aggregate summary query

Benny-950225

SSC Veteran

Points: 205
More actions
November 25, 2008 at 4:38 am

#196817

Hi
I am having difficulty using the Pivot function:
I have some SQL which generates a summary table:
select
UserID,
month(RegistrationDate) as Reg_Month,
count(PolicyNumber) as Reg_Volume
from
PCM.Register
group by
UserID,
month(RegistrationDate)
This produces the following:
UserIDReg_MonthReg_Volume
Jsmith112
Agold133
Tjones13
Jsmith27
Agold21
Tjones21
Bhancock23
Jsmith31
Jsmith41
Agold45
Jsmith51
Agold51
Tjones52
What I want to do is pivot this so that month is across the top, and the columns are populated with the sum of registration volumes in that month for each user:
UserID12345
Agold331051
Bhancock03000
Jsmith127111
Tjones31002
I tried using the pivot function as follows, but can't get the grap of it:
select
UserID,
month(RegistrationDate)
from (
select
UserID,
month(RegistrationDate) as Reg_Month,
count(PolicyNumber) as Reg_Volume
from
PCM.Register
group by
UserID,
month(RegistrationDate)
) as a
Pivot
(
count(PolicyNumber) for month(RegistrationDate)
in month(RegistrationDate)
) as b
I've searched the web, and haven't been able to get it together from what I've read. Please help - it's driving me mad! 😉

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

If you want to stick with using PIVOT, you need to explicitly list the values you want to "horizontalize" which are the month numbers. Try this:

select * from

(select UserID, month(RegistrationDate) as Reg_Month, PolicyNumber

from PCM.Register) as a

Pivot

(count(PolicyNumber) for Reg_Month

in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) as b

Another way to do this which typically performs the same or better is to use case statements:

select UserID,

sum(case when month(RegistrationDate) = 1 then 1 else 0 end) AS [1],

sum(case when month(RegistrationDate) = 2 then 1 else 0 end) AS [2],

sum(case when month(RegistrationDate) = 3 then 1 else 0 end) AS [3],

sum(case when month(RegistrationDate) = 4 then 1 else 0 end) AS [4],

sum(case when month(RegistrationDate) = 5 then 1 else 0 end) AS [5],

sum(case when month(RegistrationDate) = 6 then 1 else 0 end) AS [6],

sum(case when month(RegistrationDate) = 7 then 1 else 0 end) AS [7],

sum(case when month(RegistrationDate) = 8 then 1 else 0 end) AS [8],

sum(case when month(RegistrationDate) = 9 then 1 else 0 end) AS [9],

sum(case when month(RegistrationDate) = 10 then 1 else 0 end) AS [10],

sum(case when month(RegistrationDate) = 11 then 1 else 0 end) AS [11],

sum(case when month(RegistrationDate) = 12 then 1 else 0 end) AS [12]

from #Register

group by UserID

Benny-950225 SSC Veteran Points: 205 More actions · Answer 2

Thanks - that's great.

The pivot thing seems a bit inflexible. I'd seen some code which was dynaically setting the columns but couldn't figure out what it was doing before.

Any ideas why I can't use datename() to give the alias the month's names? Even casting to varchar didn't help.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Not sure - this works for me:

select * from

(select Somecode, datename(month,dateadd(day,someid,getdate())) as Reg_Month, SomeCodeNotNull

from testdata) as a

Pivot

(count(somecode) for Reg_Month

in ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])) as b

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Benny-950225 SSC Veteran Points: 205 More actions · Answer 5

Thanks for all the replies - you've all been a great help!

🙂

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

The PIVOT method is slower than a good ol' fashioned cross-tab... see the following article, please.

[font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]

Also, dunno if you can wait until Wednesday, but a very detailed, step-by-step article on how to setup parameterized dynamic cross tabs is coming out Wednesday morning.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)