drop table test;
create table test(name varchar2(10),val varchar2(10));
insert into test values('aaa',20);
insert into test values('aaa',30);
insert into test values('aaa',40);
insert into test values('aaa',50);
insert into test values('aaa',60);
insert into test values('bbb',15);
insert into test values('bbb',25);
insert into test values('bbb',35);
insert into test values('ccc',12);
insert into test values('ccc',22);
select * from test;
select Name,sum(decode(tmp.rn,1,val)) val1,sum(decode(tmp.rn,2,val)) val2,sum(decode(tmp.rn,3,val)) val3, sum(decode(tmp.rn,4,val)) val4 ,sum(decode(tmp.rn,5,val)) val5
from (Select test.*,row_number() over(Partition By Name Order By val) rn From test)tmp Group By Name

判断转换后的最多的列数，然后按照规律编写。

引用：http://junsansi.itpub.net/post/29894/277889

表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