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