Oracle之資料記錄字元串拼接

類似需求在SQLServer中的實作方式參考：http://topic.csdn.net/u/20090908/15/a95cce26-f9a3-4943-9ba4-5e7768d8ef79.html

以下是Oracle的實作方式：（轉載）

/* --建立表 test*/

create table test

(

NO NUMBER,

VALUE VARCHAR2(100),

NAME VARCHAR2(100)

);

/* ----插入資料*/

insert into test

select * from

(

select '1','a','測試1' from dual union all

select '1','b','測試2' from dual union all

select '1','c','測試3' from dual union all

select '1','d','測試4' from dual union all

select '2','e','測試5' from dual union all

select '4','f','測試6' from dual union all

select '4','g','測試7' from dual

);

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

/*--lead(rnFirst)：取得下一行記錄的rnFirst字段

--over(partition by No order by rnFirst) 按rnFirst排序，并按No分區，

--分區就是如果下一行的No字段與目前行的No字段不相等時，不取下一行記錄顯示*/

from (select a.No,

a.Value,

a.Name,

row_number() over(order by a.No, a.Value desc) rnFirst

from Test a) tmpTable1;

/*

--該語句結果如下：

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，去掉最前面的那個分号，完成。

*/

最後要注意的是：

“是以在sys_connect_by_path外面套一個Max，再加個Group by No，得到的結果就是行轉列的結果了” 這句話對Oracle 9i成立。Oracle 10g的查詢結果如下：

1 1 d 測試4

2 2 e 測試5

3 4 g 測試7