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博客 ” ，链接：https://blog.itpub.net/7988176/viewspace-918939/ ，如需转载，请注明出处，否则将追究法律责任。
-
博文量29
-
访问量341611
