Pivot Error

  • 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]

  • 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

  • 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