Convert rows to columns in SQL server

[krunal1986]

Hi All,

i have requirement like to Convert rows to columns in SQL server.

i have retrieved data like below:

MatchId SelectionName   Odds
    322 Home Win        1.33
    322 Draw            5.00
    322 Away Win        9.00
    322 Under 2.5       2.40
    322 Over 2.5        1.53

and want to convert like below:

MatchId   Home Win   Draw   Away Win   Under 2.5   Over 2.5
322       1.33       5.00   9.00       2.40        1.53 

how can i do this?

Thanks,
KK

[krunal1986]

select * from (select MatchId,SelectionName,Odds From 
    #tmpMatchOdds) As P Pivot
    (
        Sum([odds]) For SelectionName In ([Home Win],[Draw],[Away Win],[Under 2.5],[Over 2.5])
    )As pv

[urtrivedi]

In case of oracle/mysql you can do following and even in mssql

select MatchId
, sum(case when SelectionName='Home win' then  Odds else 0 end )home_win
, sum(case when SelectionName='Draw' then  Odds else 0 end ) draw
, sum(case when SelectionName='Away win' then  Odds else 0 end )away_win
, sum(case when SelectionName='Under 2.5' then  Odds else 0 end ) under_25
, sum(case when SelectionName='Over 2.5' then  Odds else 0 end ) over_25
group by matchid