CODEWITHSUNDEEP
sqlsql-server-2008pivot
user2850858
user2850858

Reputation: 91

Pivoting data - SQL Server 2008

I'm wondering how would I go about pivoting the following table

id     | name         | course   | participating | year
-----------------------------------------------------------------
abc    | harv       | math     | 4               | 2012
abc    | harv       | eng      | 2               | 2012
abc    | harv       | hist     | 3               | 2011

eee    | yale       | eng      | 2               | 2012
eee    | yale       | math     | 5               | 2012

ppp    | mit        | hist     | 7               | 2011
ppp    | mit        | eng      | 4               | 2012

to look like this:

id    | name        | year    | math   | eng    | hist
-------------------------------------------------------
abc   | harv        | 2011     | 0      | 0      | 3
abc   | harv        | 2012     | 4      | 2      | 0

eee   | yale        | 2012     | 5      | 2      | 0

ppp   | mit         | 2011     | 0      | 0      | 7
ppp   | mit         | 2012     | 0      | 4      | 0

Upvotes: 1

Views: 63

Answers (1)

roman
roman

Reputation: 117380

IF you don't mind having null instead of 0, you can use pivot keyword:

select *
from @temp
pivot (max(participating) for course in ([math], [eng], [hist])) as p

id   name   year    math    eng     hist
----------------------------------------
abc  harv   2011    NULL    NULL    3
abc  harv   2012    4       2       NULL
eee  yale   2012    5       2       NULL
ppp  mit    2011    NULL    NULL    7
ppp  mit    2012    NULL    4       NULL

otherwise, you can pivot manually:

select
    id, name, [year],
    isnull(max(case when course = 'math' then participating end), 0) as [math],
    isnull(max(case when course = 'eng' then participating end), 0) as [eng],
    isnull(max(case when course = 'hist' then participating end), 0) as [hist]
from @temp
group by id, name, [year]

id   name   year    math    eng hist
----------------------------------------
abc  harv   2011    0       0   3
abc  harv   2012    4       2   0
eee  yale   2012    5       2   0
ppp  mit    2011    0       0   7
ppp  mit    2012    0       4   0

Upvotes: 1

Related Questions