Friday, 15 July 2011

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, ','),
                  ) "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
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 1st row to the ‘2’ in the child_row column of the 2nd 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

No comments: