Concatenare i valori di più record

This article is also available in English here.

Tutti sappiamo come funziona la clausola GROUP BY e quando utilizzarla. Anche le funzioni di gruppo che vengono utilizzate insieme alla GROUP BY sono generalmente molto note: SUM, AVG, MIN, MAX e soprattutto la COUNT…
Capita però spesso di dover risolvere un problema “di gruppo” a cui queste funzioni non danno una risposta: Utilizzando la tabella EMP dei dipendenti (ENAME è il nome del dipendente e DEPTNO il numero del dipartimento) immaginiamo di voler estrarre un record per ogni DEPTNO che evidenzi l’elenco dei nomi dei dipendenti impiegati nel dipartimento separati da virgola.
Ecco gli impiegati presenti nella tabella EMP:

SQL> select deptno, ename
  2  from emp
  3* order by deptno

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN

E vogliamo ottenere qualcosa del genere :

    DEPTNO ENAMES
---------- ----------
        10 CLARK,KING,MILLER
        20 JONES,FORD,ADAMS,SMITH,SCOTT
        30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN

L’approccio più naturale è probabilmente la scrittura di una funzione PL/SQL:

create or replace function getNames(p_deptno in number) return varchar2 is
retval varchar2(32000);
begin
  for r in (select ename from emp where deptno = p_deptno) loop
    retval := retval||r.ename||',';
  end loop;
  return rtrim(retval,',');
end;
/

SQL> select deptno,getNames(deptno) enames
  2  from emp
  3* group by deptno

    DEPTNO ENAMES
---------- -------------------------------------------
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        10 CLARK,KING,MILLER

Quest’approccio ha però il difetto di effettuare una select (quella definita nel cursore implicito del loop, nel PL/SQL) per ogni record di EMP. Ciò può essere molto grave su grandi moli di dati.

Il secondo metodo che voglio presentare è basato su una funzione di gruppo non documentata: WM_CONCAT.
Questa funzione è definita nello schema WMSYS. Questo schema contiene gli oggetti di sistema per la funzionalità “Workspace Manager” su cui potete trovare maggiori info a questa pagina.
Si tratta di una funzione non documentata, utilizzata da Oracle per scopi interni e dunque è assolutamente sconsigliabile utilizzarla in produzione.
Però esiste e funziona una meraviglia:

SQL> select deptno, wm_concat(ename) enames
  2  from emp
  3  group by deptno;

    DEPTNO ENAMES
---------- -------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Terzo metodo che vi presento è l’utilizzo della funzione SYS_CONNECT_BY_PATH.
Questa funzione può essere utilizzata solo in query gerarchiche (CONNECT BY) prende in input una colonna ed un carattere e restituisce il path completa di ogni elemento della colonna data a partire dalla root utilizzando il carattere passato in input come separatore.
Un esempio ci chiarirà le idee:

 SELECT deptno, ltrim(SYS_CONNECT_BY_PATH(ename, ','),',') enames
  FROM (select deptno, ename, rank() over(partition by deptno order by rownum) num from emp)
where connect_by_isleaf=1
START WITH num=1
CONNECT BY PRIOR num+1 = num and prior deptno=deptno;

    DEPTNO ENAMES
---------- ---------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

Non è chiaro? Ok parliamone:
Prima di tutto sono partito da una query che mi mostra l’elenco dei dipendenti ordinati con un progressivo (num) che si incrementa ad ogni riga e si resetta al cambio di dipartimento.

SQL> select deptno, ename, rank() over(partition by deptno order by rownum) num
  2         from emp
  3  ;

    DEPTNO ENAME             NUM 
---------- ---------- ---------- 
        10 CLARK               1 
        10 KING                2 
        10 MILLER              3 
        20 SMITH               1 
        20 JONES               2 
        20 SCOTT               3 
        20 ADAMS               4 
        20 FORD                5 
        30 ALLEN               1 
        30 WARD                2 
        30 MARTIN              3 
        30 BLAKE               4 
        30 TURNER              5 
        30 JAMES               6 

Per ottenere questo risultato ho utilizzato le funzioni analitiche, disponibili in Oracle dalla versione 9i di cui ho parlato in un articolo pubblicato anni fa su Computer Programming.

A questo punto sono passato alla query gerarchica: chi fosse a digiuno di CONNECT BY può dare uno sguardo sulla documentazione.

Collego ogni ename a quello che ha lo stesso deptno e num maggiore di uno (il successivo) mediante la clausola

CONNECT BY PRIOR num+1 = num and prior deptno=deptno


Elimino a priori i rami incompleti, utilizzando la condizione

WHERE connect_by_isleaf=1

che ci permette di selezionare solo le foglie dell’albero.

Infine mi interesso solo ai rami che partono dal primo ename per ogni deptno mediante la clausola


START WITH num=1


Giocate un po’ modificando questa query, ne apprezzerete la potenza un po’ alla volta!

Quarto approccio: Query XML.

SQL> select deptno, rtrim(
  2                   xmlagg(
  3                     xmlelement("x",ename||',')
  4                          ).extract('//text()'),',') enames
  5    from emp
  6  group by deptno;

    DEPTNO ENAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Per ogni dipartimento ottengo con XMLAGG un documento XML che aggrega tutti i dipendenti che lavorano in quel dipartimento e poi estraggo solo il contenuto degli elementi utilizzando la funzione extract.

Quinto approccio: la funzione LISTAGG.
Si tratta di una nuova funzione disponibile in Oracle11g release 2. Ecco la documentazione.

SQL >SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) enames
  2    FROM emp
  3  group by deptno;

    DEPTNO ENAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Funziona alla meraviglia!!

Alla prossima,
Massimo

Tag: Aggregazione di stringhe, oracle, SQL, String aggregation