February 23, 2009 at 3:12 pm
I have a table similar to:
Create table #temp ([year] varchar(4),program varchar (20),status varchar (15))
insert into #temp
values ('2006','ER','Active'),
('2008','MVP','Active'),
('2008','ER','Active'),
('2008','MVP','Declined')
('2007','TS','Declined')
What I would like to end up with is:
Year program Active Declined
2006 ER 1
2007 TS 1
2008 MVP 1 1
2008 ER 1
Here is my select clause, but no matter what I try, I always get "invalid column name program". I have tried dropping program to see if I could get it by year, but then get "invalid column name year". I have played around with it, but cannot get it - where have I gone wrong?
select [year], program, [Active] as active, [Declined] as declined
from
(Select [year], program, status
from #temp
) g
PIVOT
(
count(status)
for [program]
IN
([Active], [Declined])
) as pvt
ORDER BY [program]
February 23, 2009 at 3:23 pm
Hi Bernadette,
You pretty much had it.... I just changed for [program] to for [status]
select [year], [program], [Active], [Declined]
from
(Select [year], program, [status] from #temp) g
PIVOT
(count(status) for [status] IN ([Active], [Declined]) ) as pvt
ORDER BY [year], [program]
Bevan
February 23, 2009 at 3:26 pm
Aaahhhh....well now that you've pointed it out, it makes perfect sense, but I would never have tried that! Thanks so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply