初级行转列示例

表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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7607759/viewspace-22207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7607759/viewspace-22207/