利用sys_connect_by_path把多行转成单行字符串
最近有几个项目都有把多行转成字符串的需求,现写一示例供大家以后参考.
创建一个表并插入数据:
CREATE TABLE t_row_str(
ID NUMBER,
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;
创建一个表并插入数据:
CREATE TABLE t_row_str(
ID NUMBER,
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;
ID | COL |
1 | a |
1 | b |
1 | c |
2 | a |
2 | d |
2 | e |
3 | c |
现用sys_connetc_by_path把字段str转换为字符串
SELECT t.id id, sys_connect_by_path(t.col, ',') 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;
ID | STR |
1 | ,a |
1 | ,a,b |
1 | ,a,b,c |
2 | ,a |
2 | ,a,d |
2 | ,a,d,e |
3 | ,c |
从上面的结果看STR字段多了一个逗号且多了几行,那现在就用substr去掉第一个逗号,再用max取最大的那一行:
结果如下:
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;
ID | STR |
1 | a,b,c |
2 | a,d,e |
3 | c |
现已成功把多行转换成单行字符串了,希望此示例对大家有用.
下面再贴上网上创业园的一示例代码:
--把同一产品的仓位及数量显示为一行
SELECT v_product_code,
MAX(substr(sys_connect_by_path(n_position_id || '/' || qt, ','), 2)) str
FROM (SELECT t.v_product_code,
t.n_position_id,
SUM(t.n_qty) qt,
row_number() over(PARTITION BY t.v_product_code ORDER BY t.v_product_code) rn
FROM tb_wh_pos_io_trace t
GROUP BY v_product_code, t.n_position_id)
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND v_product_code = PRIOR v_product_code
GROUP BY v_product_code;
V_PRODUCT_CODE STR
------------------- --------------------
518022000041 3/444,4/111
518022000042 3/111
518022000041 3/444,4/111
518022000042 3/111
转自:http://www.cnblogs.com/jimeper/archive/2010/06/04/1751180.html