Evolution...

Evolution. A frequently asked question is “how can I get a comma delimited list of values grouped by some column”. That is, given the EMP table – I would like a column that is the ‘JOB’ column and another column that is a list of all employee names that have that job assignment, the result would look like this:

JOB       ENAMES
--------- ------------------------------
ANALYST   FORD,SCOTT
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN  ALLEN,MARTIN,TURNER,WARD

How can we achieve that. This question is so frequently asked that it is in fact one of the very first asktom questions ever. Back then the release was 8i – and the answer was very different from what we would say in later releases (that thread has the entire history). Back then, a PLSQL function could be used - or if you had a fixed number of rows per group by column (eg: you knew there would never be more than 25 employees per job) you could use DECODE.

Then 9i came out with user defined aggregates and “stragg” was first born (and itself has undergone many iterations). Later someone noticed that with analytics and CONNECT BY (with the then ‘new’ sys_connect_by_path function) we could perform this operation directly in SQL.

So, the answer today – when someone asked today how to develop this comma delimited list by some “group by” set of columns and to have the list sorted would be:

ops$tkyte%ORA10GR2> with data
2  as
3  (
4  select job,
5         ename,
6         row_number() over (partition by job order by ename) rn,
7         count(*) over (partition by job) cnt
8    from emp
9  )
10  select job, ltrim(sys_connect_by_path(ename,','),',') scbp
11    from data
12   where rn = cnt
13   start with rn = 1
14  connect by prior job = job and prior rn = rn-1
15   order by job
16  /

JOB       SCBP
--------- ----------------------------------------
ANALYST   FORD,SCOTT
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN  ALLEN,MARTIN,TURNER,WARD

Lets look at each bit in a little more detail. Starting with the “WITH” subquery. I find that to be a nice construct to factor out an implementation detail. In order to do this pivot trick – we need the data broken up virtually by JOB (hence the partition by in the analytics – we partition by our GROUP BY columns – the thing we want to “distinct” on). After the data is broken up by JOB – we assign each row in each JOB set an ascending ROW_NUMBER. Since in this case – we wanted the list of employee names sorted – we order by ENAME. If you didn’t care how the list of ENAMEs was sorted – you could order by anything you wanted. We’ll also gather the COUNT of rows by JOB in this step as well. We’ll need this later to keep just the ‘last’ row from this set of rows by job.

The result of the WITH subquery would look like this:

ops$tkyte%ORA10GR2> select job,
2         ename,
3         row_number() over (partition by job order by ename) rn,
4         count(*) over (partition by job) cnt,
5         decode( row_number() over (partition by job order by ename),
6                 count(*) over (partition by job),
7                '<<<=== last one' ) tag
8    from emp
9  /

JOB       ENAME              RN        CNT TAG
--------- ---------- ---------- ---------- ---------------
ANALYST   FORD                1          2
ANALYST   SCOTT               2          2 <<<=== last one
CLERK     ADAMS               1          4
CLERK     JAMES               2          4
CLERK     MILLER              3          4
CLERK     SMITH               4          4 <<<=== last one
MANAGER   BLAKE               1          3
MANAGER   CLARK               2          3
MANAGER   JONES               3          3 <<<=== last one
PRESIDENT KING                1          1 <<<=== last one
SALESMAN  ALLEN               1          4
SALESMAN  MARTIN              2          4
SALESMAN  TURNER              3          4
SALESMAN  WARD                4          4 <<<=== last one

14 rows selected.

We have broken the data up by “JOB”, assigned the ROW_NUMBER() to each row and since the COUNT by JOB is assigned to each row – we can find the “last” row. Now all we need to do is assemble the delimited list. Enter: SYS_CONNECT_BY_PATH. This is a “new” function in 9i and above that can assemble the ‘genealogy’ of a row in a hierarchical query. For example:

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || ename emp_name,
2         sys_connect_by_path(ename,',') enames
3    from emp
4   start with mgr is null
5  connect by prior empno = mgr
6  /

EMP_NAME        ENAMES
--------------- ------------------------------
**KING          ,KING
****JONES       ,KING,JONES
******SCOTT     ,KING,JONES,SCOTT
********ADAMS   ,KING,JONES,SCOTT,ADAMS
******FORD      ,KING,JONES,FORD
********SMITH   ,KING,JONES,FORD,SMITH
****BLAKE       ,KING,BLAKE
******ALLEN     ,KING,BLAKE,ALLEN
******WARD      ,KING,BLAKE,WARD
******MARTIN    ,KING,BLAKE,MARTIN
******TURNER    ,KING,BLAKE,TURNER
******JAMES     ,KING,BLAKE,JAMES
****CLARK       ,KING,CLARK
******MILLER    ,KING,CLARK,MILLER

So, you can see how for the SMITH record – we can see that SMITH reports to FORD reports to JONES reports to KING – all in the result of the SYS_CONNECT_BY_PATH. But – how is that useful to us here? We don’t want to build a reporting hierarchy – we want a comma delimited list of names by JOB. Well, since we added that ROW_NUMBER() column, after breaking the data up by JOB and sorting by ENAME – we have added a new “reporting relationship” dimension to the data. In a given JOB, row number 1 is the parent of row number 2, is the parent of 3, is the parent of 4 and so on. So, if we were to START WITH all RN=1 rows (the first row of each job) and CONNECT BY that row to the RN=2 row for that JOB and then RN=3 for that JOB and so on, we would achieve:

ops$tkyte%ORA10GR2> with data
2  as
3  (
4  select job,
5         ename,
6         row_number() over (partition by job order by ename) rn,
7         count(*) over (partition by job) cnt
8    from emp
9  )
10  select job, ltrim(sys_connect_by_path(ename,','),',') enames
11    from data
12   start with rn = 1
13  connect by prior job = job and prior rn = rn-1
14   order by job
15  /

JOB       ENAMES
--------- ------------------------------
ANALYST   FORD
ANALYST   FORD,SCOTT
CLERK     ADAMS
CLERK     ADAMS,JAMES
CLERK     ADAMS,JAMES,MILLER
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE
MANAGER   BLAKE,CLARK,JONES
MANAGER   BLAKE,CLARK
PRESIDENT KING
SALESMAN  ALLEN
SALESMAN  ALLEN,MARTIN,TURNER
SALESMAN  ALLEN,MARTIN
SALESMAN  ALLEN,MARTIN,TURNER,WARD

14 rows selected.

Now, we have what we need there – but we have more than we need – we don’t really want all of the rows by JOB, we just want the “last row” by JOB. That is where the count came in – just add a WHERE RN=CNT and:

ops$tkyte%ORA10GR2> with data
2  as
3  (
4  select job,
5         ename,
6         row_number() over (partition by job order by ename) rn,
7         count(*) over (partition by job) cnt
8    from emp
9  )
10  select job, ltrim(sys_connect_by_path(ename,','),',') enames
11    from data
12   where rn = cnt
13   start with rn = 1
14  connect by prior job = job and prior rn = rn-1
15   order by job
16  /

JOB       ENAMES
--------- ------------------------------
ANALYST   FORD,SCOTT
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN  ALLEN,MARTIN,TURNER,WARD

There you go – the desired result, straight in SQL, no need for PLSQL (not that PLSQL is a bad thing mind you, but if we don’t have to use it we will not use it – if you can do it in SQL, do it).

It is fun to go back and read things that started some six years ago and see how the evolved over time sometimes. I like the interaction as well – I never would have thought to use analytics and sys_connect_by_path myself, especially not after having developed stragg (once you have a hammer….). I like the sys_connect_by_path approach now – might be a little more “complex” looking at first, but once you get analytics into your brain – it becomes easy.