我们通过10g所提供的WMSYS.WM_CONCAT函数即可以完成行转列的效果,它的作用是以','链接字符。
SELECT * FROM idtable;
ID NAME
----- -----
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn
-----------------------------------------------------
SELECT id,wmsys.wm_concat(name) name
FROM idtable
GROUP BY id;
ID NAME
----- -----
10 ab,bc,cd
20 hi,ij,mn
-----------------------------------------------------
SELECT id,wmsys.wm_concat(name) OVER (ORDER BY id) name
FROM idtable;
ID NAME
----- -----
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
-----------------------------------------------------
SELECT id,wmsys.wm_concat(name) OVER (ORDER BY id,name) name
FROM idtable;
ID NAME
----- -----
10 ab
10 ab,bc
10 ab,bc,cd
20 ab,bc,cd,hi
20 ab,bc,cd,hi,ij
20 ab,bc,cd,hi,ij,mn
-----------------------------------------------------
SELECT id,wmsys.wm_concat(name) OVER (PARTITION BY id) name
FROM idtable;
ID NAME
----- -----
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 hi,ij,mn
20 hi,ij,mn
20 hi,ij,mn
-----------------------------------------------------
SELECT id,wmsys.wm_concat(name) OVER (PARTITION BY id,name) name
FROM idtable;
ID NAME
----- -----
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn