用oracle分析函式進行行列轉向

用oracle分析函式進行行列轉向[@more@]

SQL> set line 20000
SQL> select * from test1;

ID NAME
--------- ----------------------------------------
1 a
2 a2
1 b
1 c
1 d
1 e
3 a3
3 c3
3 e3
4 a4
4 b4
4 c4
4 d4

13 rows selected.

轉成一列

SQL> select id, substr(max(sys_connect_by_path(name, ',' )),2) name
2 from (select id, name, row_number() over (partition by id order by name) rn
3 from test1 )
4 start with rn = 1 connect by prior rn = rn-1 and prior id = id
5 group by id order by id ;

ID NAME
--------- --------------------------------------------------------------------------------
1 a,b,c,d,e
2 a2
3 a3,c3,e3
4 a4,b4,c4,d4

轉成多列

SQL> select id,max(b1),max(b2),max(b3),max(b4),max(b5),max(b6)
2 from (
3 select id,
4 decode(substr(name,1,1),'a',name,null) b1,
5 decode(substr(name,1,1),'b',name,null) b2,
6 decode(substr(name,1,1),'c',name,null) b3,
7 decode(substr(name,1,1),'d',name,null) b4,
8 decode(substr(name,1,1),'e',name,null) b5,
9 decode(substr(name,1,1),'f',name,null) b6
10 from test1
11 ) group by id ;

ID MAX(B1) MAX(B2) MAX(B3)
--------- ---------------------------------------- ---------------------------------------- -----------
1 a b c
2 a2
4 a4 b4 c4
3 a3 c3