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

转载 2016年01月13日 13:56:15

说明一下测试环境:Oracle9i,有朋友说10g上测试结果不正确,本人没有条件,所以无法进行测试

 

表结构和数据如下(表名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,去掉最前面的那个分号,完成。

你看明白了吗?

oracle之sys_connect_by_path(行转列挺好用的)

这个函数使用之前必须先建立一个树,否则无用sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用repl...
  • lky5387
  • lky5387
  • 2009年12月09日 15:59
  • 6285

oracle行转列

针对oracle数据查询的数据,行转列 1.wm_concat函数: 例一: select c1,c2,wm_concat(c3) from T where.... group by...
  • iceriver315
  • iceriver315
  • 2014年05月04日 22:36
  • 793

oracle行转列函数

WMSYS.WM_CONCAT 函数的用法   select t.rank, t.Name from t_menu_item t;     10 CLARK     10 KING...
  • lianhao19900202
  • lianhao19900202
  • 2016年12月05日 18:03
  • 1852

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

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

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

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

行转列

Oracle行转列、列转行的Sql语句总结 多行转字符串 这个比较简单,用||或concat函数可以实现  SQL Code  1 2 ...
  • zz770750140
  • zz770750140
  • 2017年08月30日 16:53
  • 194

oracle中最简单的行转列的例子

转载自:http://www.cnblogs.com/modou/articles/1758298.html 例子一:有一个字符串,按字母拆成多行记录,SQL 语句如下: ...
  • u010050174
  • u010050174
  • 2016年06月08日 14:51
  • 446

Oracle使用decode实现行转列

HIS开发中,在各种统计时经常会用到行转列,下面简单记录一下使用decode实现行转列 表: 住院号 费用 费用分类 001 10.00 西药费 001 20.00 中药费 00...
  • wuhongyao3
  • wuhongyao3
  • 2015年06月09日 15:38
  • 3064

oracle中decode的使用和oracle中的行转列

decode其实就是if-else,如:decode(ybc,null,0,ybc),,这个意味着如果ybc列==null,则函数返回0,否则返回ybc的值。 再如,decode(变量,条件1,值1...
  • syx19930206
  • syx19930206
  • 2014年03月09日 22:01
  • 959

Oracle—decode函数行转列

创建学生,课程,成绩三张表
  • Dream_angel_Z
  • Dream_angel_Z
  • 2014年11月10日 20:29
  • 2134
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle中,用一条Sql实现任意的行转列拼接(不是decode)
举报原因:
原因补充:

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