初級行轉列示例

表a
id,name,val
1,aaa,30
2,aaa,40
3,aaa,60
4,bbb,40
5,bbb,60
6,bbb,80
得到結果
name,val1,val2,val3
aaa,30,40,60
bbb,40,60,80

Oracle中：

select Name,sum(decode(tmp.rn,1,val)) val1,sum(decode(tmp.rn,2,val)) val2,sum(decode(tmp.rn,3,val)) val3 from (Select a.*,row_number() over(Partition By Name Order By val) rn From a)tmp Group By Name

MSSQL中：

select name,max(case rc when '1' then val end) val1,max(case rc when '2' then val end) val2,max(case rc when '3' then val end) val3 from (select id,name,(select count(0)+1 from a n where n.name=t.name and n.id[@more@]