Oracle中用一条Sql实现任意的行转列拼接 多行拼接

转载 2014年05月20日 14:56:03

表结构和数据如下(表名Test):

NO VALUE  NAME
1       a       测试1
1       b       测试2
1       c       测试3
1       d       测试4
2       e       测试5
4       f        测试6
4       g       测试7

Sql语句:
select No,
       ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
       ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;

检索结果如下:

NO VALUE    NAME
1    a;b;c;d   测试1;测试2;测试3;测试4
2    e            测试5
4    f;g          测试6;测试7

简单解释一下那个Sql吧:
1、最内层的Sql(即表tmpTable1),按No和Value排序,并列出行号:
select a.No,
       a.Value,
       a.Name,
       row_number() over(order by a.No, a.Value desc) rnFirst
  from Test a
该语句结果如下:
NO VALUE NAME RNFIRST
1     d       测试4     1
1     c       测试3     2
1     b       测试2     3
1     a       测试1     4
2     e       测试5     5
4     g       测试7     6
4     f       测试6     7


2、外层的Sql(即表tmpTable2),根据No分区,取出当前行对应的下一条记录的行号字段:
select No,
       Value,
       Name,
       rnFirst,
       lead(rnFirst) over(partition by No order by rnFirst) rnNext
  from (这里是tmpTable1的SQL) tmpTable1

lead(rnFirst):取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序,并按No分区,分区就是如果下一行的No字段与当前行的No字段不相等时,不取下一行记录显示
该语句结果如下:
NO VALUE NAME RNFIRST RNNEXT
1     d        测试4     1         2
1     c        测试3      2         3
1     b        测试2     3         4
1     a        测试1     4         NULL
2     e        测试5     5         NULL
4     g        测试7     6         7
4     f         测试6     7         NULL


3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
这个大概意思就是从rnNext为null的那条记录开始,递归查找,
如果前一记录的rnFirst字段等于当前记录的rnNext字段,就把2条记录的Value用分号连接起来,
大家可以先试试下面这个没有Max和Group的Sql:
select No,
       sys_connect_by_path(Value, ';') as Value,
       sys_connect_by_path(Name, ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst

结果是:
NO VALUE       NAME
1     ;a            ;测试1
1     ;a;b         ;测试1;测试2
1     ;a;b;c     ;测试1;测试2;测试3
1     ;a;b;c;d  ;测试1;测试2;测试3;测试4
2     ;e            ;测试5
4     ;f             ;测试6
4     ;f;g          ;测试6;测试7

可以看到,每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max,再加个Group by No,得到的结果就是行转列的结果了
最后再加一个Ltrim,去掉最前面的那个分号,完成。

转自:http://www.xker.com/page/e2009/1105/84460.html


Oracle查询多个字段行转列处理实践案例

oracle中,多个字段合并式行转列查询实践案例。
  • xiaoyw
  • xiaoyw
  • 2016年03月21日 14:25
  • 2182

ORACLE 用SQL同一列的多行记录拼接成一个字符串

-- 原始数据   -- a   111   -- b   222   -- a   333   -- a   444   -- b   555   -- 最终结果   -- a   1...
  • haitaofeiyang
  • haitaofeiyang
  • 2015年10月22日 14:23
  • 2138

Oracle 把某一列的多行数据拼接为一个字符串

业务需求:在Oracle中把某一列的多行数据拼接为一个字符串,如下: 转为关键知识点sys_connect_by_path【引自度娘】在Oracle中,SYS_CONNECT_BY_PATH函数主要...
  • CrazyMo_
  • CrazyMo_
  • 2015年05月04日 11:27
  • 2855

Oracle 多行记录合并/连接/聚合字符串的几种方法

Oracle 多行记录合并/连接/聚合字符串的几种方法 怎么合并多行记录的字符串,一直是oracle新手喜欢问的SQL问题之一,关于这个问题的帖子我看过不下30个了,现在就对这个问题,进行一个...
  • haiross
  • haiross
  • 2015年12月31日 09:44
  • 7057

ORACLE:一列的多行数据拼成字符串

上篇文章写的是在ibatis中,需要返回一个字符串,却返回了多行数据,结果出现了错误。 其实,我想做的是:   查询表中的一个字段,返回了多行,就把这么多行的数据都拼成一个字符串。 ...
  • qq5132834
  • qq5132834
  • 2016年02月18日 10:38
  • 2444

Oracle中,用一条Sql实现任意的行转列拼接(不是decode)

说明一下测试环境:Oracle9i,有朋友说10g上测试结果不正确,本人没有条件,所以无法进行测试 表结构和数据如下(表名Test):NO VALUE  NAME1       a       测试1...
  • youbl
  • youbl
  • 2009年09月18日 09:20
  • 4231

Oracle行拼接问题

Oracle行拼接问题
  • suny1054142897
  • suny1054142897
  • 2015年09月09日 10:42
  • 243

oracle行转列(连接字符串函数)

方法一:wmsys.wm_concat(column)       介绍:其函数在Oracle 10g推出,在10g版本中,返回字符串类型,在11g版本中返回clob类型。括号里面的参数是列,而...
  • Melody_Susan
  • Melody_Susan
  • 2015年03月26日 11:05
  • 640

Oracle中用一条Sql实现任意的行转列拼接 多行拼接

表结构和数据如下(表名Test): NO VALUE  NAME 1       a       测试1 1       b       测试2 1       c       测试3 1 ...
  • sanyuesan0000
  • sanyuesan0000
  • 2014年05月20日 14:56
  • 1090

SQL批量行转列已经多个字段拼接

第二步:运行一下SQL,就可以得出以下拼接(合并)效果。关键点是where jobId=V_WIP_Balance.jobId   。你想要第一个显示什么,这个ID就要在视图中查出来,不然会数据有误,...
  • lirongjiancnvto
  • lirongjiancnvto
  • 2017年04月24日 08:30
  • 451
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle中用一条Sql实现任意的行转列拼接 多行拼接
举报原因:
原因补充:

(最多只允许输入30个字)