Oracle uses an SQL statement to concatenate rows, columns, and multiple rows.

The table structure and data are as follows (table name Test): NOVALUENAME1a Test 11b Test 21c Test 31d Test 42e Test 54f Test 64g Test 7Sql statement: selectNo, ltrim (max (sys_connect_by_path (Value,;),;) asValue, ltrim (max (sys_connect_by_path (Name ,;)),

The table structure and data are as follows (table name Test ): no value name 1 a Test 1 B Test 2 1 c Test 3 1 d Test 4 2 e test 5 4 f Test 6 4g Test 7 SQL statement: select No, ltrim (max (sys_connect_by_path (Value, ';'), ';') as Value, ltrim (max (sys_connect_by_path (Name ,';')),'

The table structure and data are as follows (table name Test ):

NO VALUE NAME
1 a Test 1
1 B Test 2
1 c Test 3
1 d Test 4
2 e test 5
4 f Test 6
4g Test 7

SQL statement:
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;

The search result is as follows:

NO VALUE NAME
1 a; B; c; d Test 1; Test 2; Test 3; Test 4
2 e test 5
4 f; g Test 6; Test 7

Briefly explain the SQL statement:
1. the inmost SQL statement (Table tmpTable1) is sorted by No and Value and the row number is listed:
Select a. No,
A. Value,
A. Name,
Row_number () over (order by a. No, a. Value desc) rnFirst
From Test
The result of this statement is as follows:
NO VALUE NAME RNFIRST
1 d Test 4 1
1 c Test 3 2
1 B Test 2 3
1 a Test 1 4
2 e test 5 5
4g Testing 7 6
4 f Test 6 7

2. The outer SQL statement (Table tmpTable2) extracts the row number field of the next record corresponding to the current row based on the No partition:
Select No,
Value,
Name,
RnFirst,
Lead (rnFirst) over (partition by No order by rnFirst) rnNext
From (here is the SQL of tmpTable1) tmpTable1

Lead (rnFirst): gets the rnFirst field of the next record.
Over (partition by No order by rnFirst) is sorted by rnFirst and partitioned by No. If the No field in the next row is not equal to the No field in the current row, show without removing a line of record
The result of this statement is as follows:
NO VALUE NAME RNFIRST RNNEXT
1 d Test 4 1 2
1 c Test 3 2 3
1 B Test 2 3 4
1 a Test 1 4 NULL
2 e test 5 5 NULL
4g Testing 7 6 7
4 f Test 6 7 NULL

3. Finally, sys_connect_by_path function and start recursion are performed.
Sys_connect_by_path (Value ,';')
Start with rnNext is null
Connect by rnNext = prior rnFirst
This probably means that from the record whose rnNext is null, recursive search,
If the rnFirst field of the previous record is equal to the rnNext field of the current record, the values of the two records are connected by semicolons,
You can try the following SQL statement without Max and Group:
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

The result is:
NO VALUE NAME
1; a; Test 1
1; a; B; Test 1; Test 2
1; a; B; c; Test 1; Test 2; Test 3
1; a; B; c; d; Test 1; Test 2; Test 3; Test 4
2; e; Test 5
4; f; Test 6
4; f; g; Test 6; Test 7

We can see that the last record of each No is what we want.
Therefore, a Max is set out of sys_connect_by_path and a Group by No is added. The result is the row-to-column conversion result.
Add an Ltrim, remove the semicolon at the top, and complete the process.

Http://www.xker.com/page/e2009/1105/84460.html