Jak pokazać dane z wielu wierszy - jako listę po przecinku?

Tabele w bazach danych są bardzo proste - składają się z kolumn i wierszy. Tylko tyle i aż tyle. 

Nieraz jednak dane z tabel musimy przedstawić w nieco bardziej wysublimowanej formie. 

Tym razem chcielibyśmy zaprezentować dane z wielu wierszy  - jako listę z wartościami oddzielonymi przecinkami.

W przykładzie skorzystam ze schematu HR i tabeli EMPLOYEES na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.

W tabeli employees mamy dane pracowników i departamentów.

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

Chcielibyśmy trochę przeformatować raport tak, by w jednym wierszu pokazać id departamentu oraz kolumnę, z nazwiskami wszystkich pracowników danego departamentu.

Zeby wykonać taką konwersję skorzystamy z funkcji LISTAGG.

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                          

A co jeśli chcielibyśmy mieć dodatkowo informacje każdego pracownika np jego identyfikator? Możemy to zrobić używając funkcji LISTAGG wraz funkcjami analitycznymi.

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. 

Używając funkcji LISTAGG należy pamiętać, że przy dużej ilości danych możemy przekroczyć długość zmiennej varchar i otrzymać błąd ORA-01489

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.

Do wersji 12 niewiele można z tym było zrobić. Na szczęście w nowszych bazach Oracle mamy już wpływ na zachowanie systemu w takiej sytuacji.

Możemy nic nie zrobić czyli pozwolić bazie zgłosić błąd. Żeby było oczywiste, że jest to zamierzone działanie, możemy dodać klauzulę ON OVERFLOW ERROR. Jest to klauzula domyślna. 

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.

Możemy również obciąć zbyt długi string i dodać znacznik, że tekst był obcięty. Możemy też wyliczyć ilość obciętych znaków. 

ON OVERFLOW TRUNCATE  char WITH COUNT  - with count jest opcją domyślną

char - znak na końcu obciętego tekstu

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)  

Jeśli zaś nie chcemy pokazywać ilości obciętych znaków zmieniamy klauzulę na 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

Można również pokusić się o wykorzystanie funkcji LISTAGG do pobrania danych z innej tabeli.

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                                                         

Niemniej jest to bardzo niewydajna konstrukcja. Zapytanie do tabeli employees zostanie wykonane tyle razy, ile mamy wierszy. 

Czy LISTAGG się przydaje w praktyce? Być może nie jest najczęściej wykorzystywana funkcja, niemniej nie ukrywam, że kilka razy się przydała. Także na pewno warto o niej pamiętać.