Sys_Connect_By_Path

Purpose

SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

[@more@]

EG

Table：z_test( id int, name varchar2(20))

id	name
9	a
9	b
9	d
10	c
10	e

SQL Statements：

Select id ,

ltrim(max( sys_connect_by_path( name , ',' ) ) , ',') results

From

( Select id , name ,

row_number( )over( partition by id order by name ) pno,

row_number( )over( partition by id order by name ) - 1 sno

From z_test )

Start with pno = 1

Connect by prior pno = sno And prior id = id

Group by id;

Results：

id	Results
9	a,b,d
10	c,e

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7988176/viewspace-918939/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7988176/viewspace-918939/