Query to get a character separated list from a result set

I wouldn’t take the credit of this but I found a very cool way of converting an array to a string using hierarchical queries. I found this mechanism in one of my projects and I think it is good enough to be shared

SELECT     last_name, rn child_row, rn + 1 parent_row, cnt "Total count",

           SYS_CONNECT_BY_PATH (last_name, ',') "Concatenated String",

           SUBSTR (SYS_CONNECT_BY_PATH (last_name, ','),

                   2

                  ) "Neater String"

      FROM (SELECT last_name, ROW_NUMBER () OVER (ORDER BY employee_id) rn,

                   COUNT (*) OVER () cnt

              FROM employees

             WHERE manager_name = 'Some value')

     WHERE rn = cnt

START WITH rn = 1

CONNECT BY rn = PRIOR rn + 1

To understand the process of connecting, let's assume that the following result set is fetched by the above query for manager_name = 'Some value' 
 

We basically assign numbers to each row in the inner query, using the ROW_NUMBER analytical function, and then connecting each row to the following row using

CONNECT BY rn = PRIOR rn + 1

The side which has PRIOR next to it, is the parent. So in our case (rn + 1) is the parent. CONNECT BY clause connects parent to child. So we are connecting the value ‘2’ in parent_row column of the 1^st row to the ‘2’ in the child_row column of the 2^nd row and so on

We start this process at rn  =  1 (START WITH rn = 1) and then we pick only the last row of the entire result set (WHERE rn = cnt) because the last row will have all the connected values

SYS_CONNECT_BY_PATH function in the select clause helps us concat the values connected by the ‘CONNECT BY’ clause

Another operator worth considering is CONNECT_BY_ROOT.

Details on

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/operators004.htm#i1035022