多行转字符串

Question

WITH A AS 
(
SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL 
SELECT 1 ID,'b' col,'DE'c2,'DERE' C3 FROM dual UNION ALL
SELECT 1 ID,'c' col,'R5'c2,'DTRE' C3 FROM dual UNION ALL
SELECT 2 ID,'d' col,'D'c2,'DYE' C3 FROM dual UNION ALL
SELECT 2 id,'e' col,'ad545s'c2,'DRE' C3 FROM dual 
)现在要得到：ID COL C2         C3
1  a  ads;DE;R5   DE;DERE;DTRE  
1  b  ads;DE;R5   DE;DERE;DTRE
1  c  ads;DE;R5   DE;DERE;DTRE
2  d  D;ad545s    DYE;DRE
2  e  D;ad545s    DYE;DRE----------------
WITH A AS 
(
SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL 
SELECT 1 ID,'a' col,'DE'c2,'DERE' C3 FROM dual UNION ALL
SELECT 1 ID,'c' col,'R5'c2,'DTRE' C3 FROM dual UNION ALL
SELECT 2 ID,'d' col,'D'c2,'DYE' C3 FROM dual UNION ALL
SELECT 2 id,'e' col,'ad545s'c2,'DRE' C3 FROM dual 
)
SELECT t.ID ID, t.col, substr(sys_connect_by_path(t.c2, ';'), 2) str,substr(sys_connect_by_path(t.C3, ';'), 2) str   
  FROM (SELECT id, col,C2,c3, row_number() over(PARTITION BY id ORDER BY col) rn1  
          FROM A) t   
WHERE CONNECT_BY_ISLEAF = 1   
START WITH rn1 = 1 
CONNECT BY rn1 = PRIOR rn1 + 1  
       AND ID = PRIOR ID;  这是我的办法，没有实现。。

Answer 1

WITH A AS 
(
SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL 
SELECT 1 ID,'b' col,'DE'c2,'DERE' C3 FROM dual UNION ALL
SELECT 1 ID,'c' col,'R5'c2,'DTRE' C3 FROM dual UNION ALL
SELECT 2 ID,'d' col,'D'c2,'DYE' C3 FROM dual UNION ALL
SELECT 2 id,'e' col,'ad545s'c2,'DRE' C3 FROM dual 
)
select a.id,a.col,a2.c2,a2.c3
from a,
(select id,replace(wm_concat(a1.c2),',',';') c2,replace(wm_concat(a1.c3),',',';') c3 from a a1 group by id) a2
where a.id=a2.idrownum  ID COL C2         C3
1 1 a ads;DE;R5 DE;DTRE;DERE
2 1 b ads;DE;R5 DE;DTRE;DERE
3 1 c ads;DE;R5 DE;DTRE;DERE
4 2 d D;ad545s DYE;DRE
5 2 e D;ad545s DYE;DRE不过ORACLE已经不推荐使用wm_concat函数，建议自己写一个字符串聚合函数来实现。

Answer 2

 就是不能用 wm_concat 这个东西太不稳定

Answer 3

你去查下 这个函数，我用过一次， 隐约记得不同版本的oracle这个函数貌似好像不一样。 而且oracle官方意思不确定未来会不会对这个函数做其他修改， 要是哪天他改了你写的代码，怎么办？

Answer 4

WITH test1 AS 
(
SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL 
SELECT 1 ID,'b' col,'DE'c2,'DERE' C3 FROM dual UNION ALL
SELECT 1 ID,'c' col,'R5'c2,'DTRE' C3 FROM dual UNION ALL
SELECT 2 ID,'d' col,'D'c2,'DYE' C3 FROM dual UNION ALL
SELECT 2 ID,'e' COL,'ad545s'C2,'DRE' C3 FROM DUAL 
)
SELECT T1.ID, T1.COL,T2.CC2 C2, T2.CC3 C3 FROM TEST1 T1 JOIN 
(select id, replace(wm_concat(c2),',',';') cc2,replace(wm_concat(c3),',',';') cc3 from test1 group by id) t2 on t1.id = t2.id;

Answer 5

或者楼主可以使用listagg()的分析函数来实现。SQL> col col format A3;
SQL> col c2 format A20;
SQL> col c3 format A20;
SQL> set linesize 100 pagesize 100;
SQL> WITH t AS
  2   (
  3   SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL
  4   SELECT 1 ID,'b' col,'DE'c2,'DERE' C3 FROM dual UNION ALL
  5   SELECT 1 ID,'c' col,'R5'c2,'DTRE' C3 FROM dual UNION ALL
  6   SELECT 2 ID,'d' col,'D'c2,'DYE' C3 FROM dual UNION ALL
  7   SELECT 2 id,'e' col,'ad545s'c2,'DRE' C3 FROM dual
  8   )
  9   select id,col,
 10          listagg(c2,';')within group(order by col)over(partition by id) c2,
 11          listagg(c3,';')within group(order by col)over(partition by id) c3
 12  from t order by id,col;        ID COL C2                   C3
---------- --- -------------------- --------------------
         1 a   ads;DE;R5            DE;DERE;DTRE
         1 b   ads;DE;R5            DE;DERE;DTRE
         1 c   ads;DE;R5            DE;DERE;DTRE
         2 d   D;ad545s             DYE;DRE
         2 e   D;ad545s             DYE;DRESQL>