How to show rows like a list?

Tables in databases are very simple - they consist of columns and rows. However, sometimes the data from the tables must be presented in a slightly more sophisticated form. And here the numerous functions available to us come to the rescue.

This time we would like to present data from many rows - as a list with comma-separated values.

For this example, I will use the default HR schema and the EMPLOYEES table in Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.

In the employees table, we have employees and departments data.

SELECT department_id, last_name
FROM   employees
where department_id in (20, 30)
ORDER BY department_id;

DEPARTMENT_ID LAST_NAME                
------------- -------------------------
           20 Hartstein                
           20 Fay                      
           30 Raphaely                 
           30 Khoo                     
           30 Baida                    
           30 Tobias                   
           30 Himuro                   
           30 Colmenares               

8 rows selected

We would like to convert this report a little so that we have a department id and a column with all the employees of this department in one line.

We will use the LISTAGG function.

SELECT department_id, LISTAGG(last_name, ','  ) WITHIN GROUP (ORDER BY employee_id)
FROM   employees
where department_id in (20, 30)
GROUP BY department_id
ORDER BY department_id;


DEPARTMENT_ID LIST                                                                  
------------- ----------------------------------------------------------------------
           20 Hartstein,Fay                                                         
           30 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          

And what if we would like to have information at the level of a single employee, e.g. his employee_id and at the same time information about all employees of a given department? We can do this using the LISTAGG function together with analytical functions.

SELECT department_id, employee_id,  LISTAGG(last_name, ','  ) WITHIN GROUP (ORDER BY employee_id) over( partition by department_id) list
FROM   employees
ORDER BY department_id;
where department_id in (20, 30)

DEPARTMENT_ID EMPLOYEE_ID LIST                                                                  
------------- ----------- ----------------------------------------------------------------------
           20         202 Hartstein,Fay                                                         
           20         201 Hartstein,Fay                                                         
           30         114 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          
           30         117 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          
           30         115 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          
           30         116 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          
           30         118 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          

           30         119 Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares                          

8 rows selected. 

We should keep in mind that with a large amount of data converted to an Oracle list, it may return an error.

SELECT department_id dep_id, LISTAGG(last_name, ','   ) WITHIN GROUP (ORDER BY employee_id) list
FROM   employees,
      (SELECT level FROM dual CONNECT BY level <= 100)
where department_id in (20, 30, 80)
ORA-01489: napis otrzymany w wyniku konkatenacji jest zbyt długi
GROUP BY department_id
ORDER BY department_id;

01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

Up to version 12, not much could be done about it. Fortunately, in newer Oracle databases we already have an influence on the system behavior in such a situation.

For example, we can do nothing, let Oracle report a bug. To make it obvious that this is the intended action, we can add the ON OVERFLOW ERROR clause. This is the default behavior.

SELECT department_id dep_id, LISTAGG(last_name, ','  on overflow error ) WITHIN GROUP (ORDER BY employee_id) 
FROM   employees,
      (SELECT level FROM dual CONNECT BY level <= 100)
where department_id in (20, 30)
GROUP BY department_id
ORA-01489: napis otrzymany w wyniku konkatenacji jest zbyt długi
ORDER BY department_id;

01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

We can also cut the too-long string, add a tag that the text was cut off, and even count the number of cut characters.

ON OVERFLOW TRUNCATE char WITH COUNT - with the count is the default option

char - the character at the end 

SELECT department_id dep_id, LISTAGG(last_name, ','  on overflow truncate '(...)' with count) WITHIN GROUP (ORDER BY employee_id) list
FROM   employees,
      (SELECT level FROM dual CONNECT BY level <= 100)
where department_id in (20, 30, 80)
GROUP BY department_id

    DEP_ID LIST                                                                  
---------- ----------------------------------------------------------------------
           Fay,Fay,Fay,Fay,Fay,Fay,Fay,Fay,Fay                                   
        20 Hartstein,Hartstein,Hartstein,Hartstein,Hartstein,(..)Fay,Fay,Fay,Fay,


        30 Raphaely,Raphaely,Raphaely,Raphaely,Raphaely,Rapha(..)lmenares,Colmena
           res,Colmenares,Colmenares,(...)(49)                                   

        80 Russell,Russell,Russell,Russell,Russell,Russell,Ru(..)Zlotkey,Zlotkey,
           Zlotkey,Zlotkey,Zlotkey,(...)(2967)  

If we do not want to show the number of truncated characters, we change the clause to WITHOUT COUNT

SELECT department_id dep_id, LISTAGG(last_name, ','  on overflow truncate '(...)' without count) WITHIN GROUP (ORDER BY employee_id) list
FROM   employees,
      (SELECT level FROM dual CONNECT BY level <= 100)
where department_id in (20, 30, 80)
GROUP BY department_id


    DEP_ID LIST                                                                  
---------- ----------------------------------------------------------------------
           Fay,Fay,Fay,Fay,Fay,Fay,Fay,Fay,Fay                                   
        20 Hartstein,Hartstein,Hartstein,Hartstein,Hartstein,(..)Fay,Fay,Fay,Fay,


        30 Raphaely,Raphaely,Raphaely,Raphaely,Raphaely,Rapha(..)s,Colmenares,Col
           menares,Colmenares,Colmenares,(...)                                   
           otkey,Zlotkey,Zlotkey,Zlotkey,(...)  
        80 Russell,Russell,Russell,Russell,Russell,Russell,Ru(..)otkey,Zlotkey,Zl

You can also use the LISTAGG function to retrieve data from another table.

SELECT department_name, 
       (select listagg(last_name, ',')  WITHIN GROUP (ORDER BY employee_id) from employees where department_id = dept.department_id) list
from departments dept
where department_id in (20, 30); 
DEPARTMENT_NAME                LIST                                                                  
------------------------------ ----------------------------------------------------------------------
Purchasing                     Raphaely,Khoo,Baida,Tobias,Himuro,Colmenares   
Marketing                      Hartstein,Fay                                                         

Nevertheless, it is a very inefficient construction. The query to the employees table will be executed as many times as there are rows.

Is LISTAGG useful in practice? Perhaps this is not the most used function, but I must admit that it came in handy several times. Also definitely worth remembering about it.