0

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

Edited by krunal1986

2
Contributors
2
Replies
11
Views
4 Years
Discussion Span
Last Post by urtrivedi
0
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
0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.