oracle 行转列的例子

with test as
(select '1' bit from dual union select '0' from dual )
select replace(sys_connect_by_path(bit,'#'),'#') combo
from test
where level=5
connect by level<=5
order by combo;

CREATE TABLE t_row_str( 
ID INT, 
col VARCHAR2(10)); 
INSERT INTO t_row_str VALUES(1,'a'); 
INSERT INTO t_row_str VALUES(1,'b'); 
INSERT INTO t_row_str VALUES(1,'c'); 
INSERT INTO t_row_str VALUES(2,'a'); 
INSERT INTO t_row_str VALUES(2,'d'); 
INSERT INTO t_row_str VALUES(2,'e'); 
INSERT INTO t_row_str VALUES(3,'c'); 
COMMIT; 
SELECT * FROM t_row_str;

 

--MAX + DECODE 

--适用范围：8i,9i,10g及以后版本
SELECT id,
MAX(decode(rn, 1, col, NULL)) || MAX(decode(rn, 2, ',' || col, NULL)) || MAX(decode(rn, 3, ',' || col, NULL)) str
FROM (SELECT id,
col,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;
--ROW_NUMBER + LEAD
--适用范围：8i,9i,10g及以后版本
SELECT id
,str
FROM (SELECT id,
row_number() over(PARTITION BY id ORDER BY col) AS rn,
col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col)
|| lead(',' || col, 2) over(PARTITION BY id ORDER BY col) AS str
FROM t_row_str)
WHERE rn = 1
ORDER BY 1;
--MODEL
--适用范围：10g及以后版本
SELECT id,
substr(str, 2) str
FROM t_row_str
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT
ITERATE(3) UNTIL( presentv(str[iteration_number+1],1,0)=0)
(str[0] = str[0] || ',' || str[iteration_number+1])
ORDER BY 1;
--SYS_CONNECT_BY_PATH
--适用范围：8i,9i,10g及以后版本
SELECT t.id id
, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT id
, col
, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
--适用范围：10g及以后版本
SELECT t.id id
, substr(sys_connect_by_path(t.col, ','), 2) str
FROM (SELECT id
, col
, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id;
--WMSYS.WM_CONCAT
--适用范围：10g及以后版本
--这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/') str
FROM t_row_str
GROUP BY id;

感谢同事zhangxiao的共享,