多行转字符串

softwakaka 2013-04-07 03:30:04

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;

这是我的办法，没有实现。。

...全文

264 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

flighting_sky 2013-08-07

打赏
举报

或者楼主可以使用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;DRE

SQL>

cutebear2008 2013-04-11

打赏
举报

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;

softwakaka 2013-04-08

打赏
举报

我决定用我之前的代码结合左联去搞。哪位有一次性搞出的方法，求教。

softwakaka 2013-04-08

打赏
举报

引用 4 楼 softwakaka 的回复:

引用 3 楼 ilovemk 的回复: 引用 2 楼 softwakaka 的回复: 引用 1 楼 vanjayhsu 的回复:SQL code ? 123456789101112 WITH A AS ( SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL SELECT 1 ID,'b' col,'DE'c2,'……

引用 4 楼 softwakaka 的回复:

引用 3 楼 ilovemk 的回复: 引用 2 楼 softwakaka 的回复: 引用 1 楼 vanjayhsu 的回复:SQL code ? 123456789101112 WITH A AS ( SELECT 1 ID,'a' col,'ads'c2,'DE' C3 FROM dual UNION ALL SELECT 1 ID,'b' col,'DE'c2,'……

逗号点错，要是哪天他改了，你写的代码怎么办？

softwakaka 2013-04-08

打赏
举报

引用 3 楼 ilovemk 的回复:

引用 2 楼 softwakaka 的回复: 引用 1 楼 vanjayhsu 的回复:SQL code ? 123456789101112 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 UN……

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

ilovemk 2013-04-07

打赏
举报

引用 2 楼 softwakaka 的回复:

引用 1 楼 vanjayhsu 的回复:SQL code ? 123456789101112 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 ALLSELE……

哪里不稳定了？

softwakaka 2013-04-07

打赏
举报

引用 1 楼 vanjayhsu 的回复:

SQL code ? 123456789101112 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 ALLSELECT 1 ID,'c' col,'R5'c2,'DTRE' C3 F……

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

vanjayhsu 2013-04-07

打赏
举报

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.id

rownum 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函数，建议自己写一个字符串聚合函数来实现。