多行转字符串

最新推荐文章于 2024-03-07 15:30:52 发布

flighting_sky

最新推荐文章于 2024-03-07 15:30:52 发布

阅读量890

点赞数

CC 4.0 BY-SA版权

分类专栏： PL/SQL Programming 文章标签：多行转字符串

本文链接：https://blog.youkuaiyun.com/flighting_sky/article/details/9815599

PL/SQL Programming 专栏收录该内容

16 篇文章

订阅专栏

本文总结了多行转字符串问题，重点讲解了SQL中的listagg()函数用法，包括其语法、注意事项及基本示例。通过分析listagg()函数的特性，结合sys_connect_by_path()和row_number()等方法，解释了如何在处理数据时消除重复行并构建树结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

经常在论坛上看到有人问多行转字符串的问题，下面对这类问题进行总结一下：

一：分析函数listagg()

语法：listagg(string,separator)within group(order-by-clause)over(partition-by-clause);

需要注意的是，由于listagg()不会聚合，为此有可能需要distinct来消除重复的行。

/**在论坛上看到有人想把下面的数据一转换成数据二的格式，
 **显然是多行转字符串的问题，同时数据二的格式中行数不变
 **正好使用分析函数相当方便；
 **/
--数据一：

        ID COL C2                   C3
---------- --- -------------------- --------------------
         1 a   ads                  DE
         1 b   DE                   DERE
         1 c   R5                   DTRE
         2 d   D                    DYE
         2 e   ad545s               DRE
--数据二：

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

二、使用connect by递归

基本用法：

使用sys_connect_by_path(string,separator)连接,注意的是，separator先于string。

start with:启动根节点；

connect by prior：确定父节点与子节点；

该方法需要注意的是：

1、sys_connect_by_path()在每一层次都会有连接输出，为此一般取最大的那个字符串即可；

2、需要借助row_number()over(partition-by-clause order-by-clause )来对数据进行分组排号；

3、使用之前，重要的是根据连接，规划出树结构；

SQL> col col format A6;
SQL> with t as
  2  (select 1 as id,'a' as col from dual union all
  3   select 1,'b' from dual union all
  4   select 2,'c' from dual union all
  5   select 3,'d' from dual union all
  6   select 3,'e' from dual union all
  7   select 3,'f' from dual
  8   )
  9  select id,
 10         ltrim(max(sys_connect_by_path(col,',')),',') col
 11  from (select id,
 12               col,
 13               row_number()over(partition by id order by col) num
 14        from t) items
 15  start with num =1
 16  connect by prior num=num-1
 17         and prior id =id
 18  group by id
 19  order by id;

        ID COL
---------- ------
         1 a,b
         2 c
         3 d,e,f