oracle11g行列转换

列转行

select * from t;

1 1 a

2 2 b

3 3 c

4 3 d

select id,listagg(name,',') within group(order by name) from t group by id;

3 3 c,d

行转列

with a as 

 (select 'tom' name,2 score,'sql' course from dual

 union

 select 'tom' name,4 score,'sql' course from dual

 select 'tom' name,4 score,'java' course from dual

 select 'jack' name,2 score,'sql' course from dual

 select 'jack' name,5 score,'java' course from dual)

select *

  from a pivot(sum(score) for course in('sql','java'));

1 tom 6 4

2 jack 2 5

要是想显示所有的行的信息，不累加，那么可以用

  from a pivot(listagg(score,',') within group(order by course) for course in('sql','java'));

1 tom 2,4 4