Oracle之数据记录字符串拼接

类似需求在SQLServer中的实现方式参考：http://topic.csdn.net/u/20090908/15/a95cce26-f9a3-4943-9ba4-5e7768d8ef79.html

 

以下是Oracle的实现方式：（转载）

[c-sharp] view plain copy

1. /* --创建表 test*/  
2. create table test  
3. (  
4.   NO   NUMBER,  
5.   VALUE    VARCHAR2(100),  
6.   NAME     VARCHAR2(100)  
7. );  
8. /* ----插入数据*/  
9. insert into test  
10. select * from   
11. (  
12.   select '1','a','测试1' from dual union all  
13.   select '1','b','测试2' from dual union all  
14.   select '1','c','测试3' from dual union all  
15.   select '1','d','测试4' from dual union all  
16.   select '2','e','测试5' from dual union all  
17.   select '4','f','测试6' from dual union all  
18.   select '4','g','测试7' from dual  
19. );  
20. /*--Sql语句：*/  
21. select No,  
22.        ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,  
23.        ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name  
24.   from (select No,  
25.                Value,  
26.                Name,  
27.                rnFirst,  
28.                lead(rnFirst) over(partition by No order by rnFirst) rnNext  
29.           from (select a.No,  
30.                        a.Value,  
31.                        a.Name,  
32.                        row_number() over(order by a.No, a.Value desc) rnFirst  
33.                   from Test a) tmpTable1) tmpTable2  
34.  start with rnNext is null  
35. connect by rnNext = prior rnFirst  
36.  group by No;  
37.    
38. /*--检索结果如下：*/  
39. /* 
40. NO VALUE    NAME 
41. 1    a;b;c;d   测试1;测试2;测试3;测试4 
42. 2    e            测试5 
43. 4    f;g          测试6;测试7 
44. */  
45.   
46.   
47. /********************************* 分析 *************************************/  
48. --简单解释一下那个Sql吧：  
49. /*--1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：*/  
50. select a.No,  
51.        a.Value,  
52.        a.Name,  
53.        row_number() over(order by a.No, a.Value desc) rnFirst  
54.   from Test a;  
55. /* 
56. 该语句结果如下： 
57. NO VALUE NAME RNFIRST 
58. 1     d       测试4     1 
59. 1     c       测试3     2 
60. 1     b       测试2     3 
61. 1     a       测试1     4 
62. 2     e       测试5     5 
63. 4     g       测试7     6 
64. 4     f       测试6     7 
65. */  
66. /*--2、外层的Sql（即表tmpTable2），根据No分区，取出当前行对应的下一条记录的行号字段：*/  
67. select No,  
68.        Value,  
69.        Name,  
70.        rnFirst,  
71.        lead(rnFirst) over(partition by No order by rnFirst) rnNext  
72.        /*--lead(rnFirst)：取得下一行记录的rnFirst字段 
73.         --over(partition by No order by rnFirst) 按rnFirst排序，并按No分区， 
74.         --分区就是如果下一行的No字段与当前行的No字段不相等时，不取下一行记录显示*/  
75.   from (select a.No,  
76.                a.Value,  
77.                a.Name,  
78.                row_number() over(order by a.No, a.Value desc) rnFirst  
79.           from Test a) tmpTable1;  
80.             
81. /* 
82. --该语句结果如下： 
83. NO VALUE NAME RNFIRST RNNEXT 
84. 1     d        测试4     1         2 
85. 1     c        测试3      2         3 
86. 1     b        测试2     3         4 
87. 1     a        测试1     4         NULL 
88. 2     e        测试5     5         NULL 
89. 4     g        测试7     6         7 
90. 4     f         测试6     7         NULL 
91. */  
92.   
93.   
94. /*--3、最后就是最外层的sys_connect_by_path函数与start递归了*/  
95. sys_connect_by_path(Value, ';')  
96.  start with rnNext is null  
97. connect by rnNext = prior rnFirst  
98. /* 
99. --这个大概意思就是从rnNext为null的那条记录开始，递归查找， 
100. --如果前一记录的rnFirst字段等于当前记录的rnNext字段，就把2条记录的Value用分号连接起来， 
101. --大家可以先试试下面这个没有Max和Group的Sql： 
102. */  
103. select No,  
104.        sys_connect_by_path(Value, ';') as Value,  
105.        sys_connect_by_path(Name, ';') as Name  
106.   from (select No,  
107.                Value,  
108.                Name,  
109.                rnFirst,  
110.                lead(rnFirst) over(partition by No order by rnFirst) rnNext  
111.           from (select a.No,  
112.                        a.Value,  
113.                        a.Name,  
114.                        row_number() over(order by a.No, a.Value desc) rnFirst  
115.                   from Test a) tmpTable1) tmpTable2  
116.  start with rnNext is null  
117. connect by rnNext = prior rnFirst  
118.   
119. /* 
120. 结果是： 
121. NO    VALUE       NAME 
122. 1     ;a            ;测试1 
123. 1     ;a;b         ;测试1;测试2 
124. 1     ;a;b;c     ;测试1;测试2;测试3 
125. 1     ;a;b;c;d  ;测试1;测试2;测试3;测试4 
126. 2     ;e            ;测试5 
127. 4     ;f             ;测试6 
128. 4     ;f;g          ;测试6;测试7 
129. */  
130.   
131. /* 
132. --可以看到，每个No的最后一条记录就是我们要的了 
133. --所以在sys_connect_by_path外面套一个Max，再加个Group by No，得到的结果就是行转列的结果了 
134. --最后再加一个Ltrim，去掉最前面的那个分号，完成。 
135. */  

 

最后要注意的是：

 

“所以在sys_connect_by_path外面套一个Max，再加个Group by No，得到的结果就是行转列的结果了” 这句话对Oracle 9i成立。Oracle 10g的查询结果如下：

 

1 1 d 测试4
2 2 e 测试5
3 4 g 测试7