Sys_connect_by_path Functions in Oracle

Create TableTest (Avarchar2(Ten), bvarchar2(Ten)); INSERT  intoTEST (A, B)VALUES('1','I'm'); INSERT  intoTEST (A, B)VALUES('1','our'); INSERT  intoTEST (A, B)VALUES('2','a'); INSERT  intoTEST (A, B)VALUES('2','from'); COMMIT; SELECTA, B fromTEST A B---------- ----------  1I'm1our2a2It is now necessary to achieve the following effect, A B---------- ----------  1me, guys.2One, up

Just want to use one sentence of SQL to return the result

SELECTALTRIM(MAX(Sys_connect_by_path (B,',')),',') B from(SELECTB, A, Row_number () Over(PARTITION byAORDER  byBDESC) RN fromTEST) START withRn= 1CONNECT byRn- 1 =PRIOR RN andA=PRIOR AGROUP  byA

The main function of the Sys_connect_by_path function is that all child nodes under a parent node can be distinguished by a character, and then the connection is displayed in a column.

The purpose of the Row_number function is very extensive, and the function is to generate a sequence number for each row of records that are queried. The method of producing the serial number is controlled by the statement inside the over () function.

Excerpt from: http://database.51cto.com/art/201010/231125.htm

Sys_connect_by_path functions in Oracle