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

表结构和数据如下(表名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, ';')), '

表结构和数据如下(表名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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！