(原创)利用sys_connect_by_path把多行转成单行字符串

利用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;

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