SQL SERVER - Più valori in un singolo campo con STUFF

 A volte è necessario recuperare più di un valore associato ad un record, ma senza produrre più record "duplicati".

Ad esempio, vorremmo ottenere l'elenco degli utenti con i gruppi ad esso associati o un elenco di utenti (per nome) con tutti gli account associati.

Nel primo caso, potremmo avere questa lista di utenti nella tabella "sys_utenti":

utente
----------------------------------------
m.rossi
a.bianchi
p.verdi

Con questi permessi nella tabella "sys_gruppiUtente":

utente     | gruppo
----------------------------------------
m.rossi    | admin
m.rossi    | ufficioIT
a.bianchi  | ufficioAmministrazione
p.verdi    | segreteria
p.verdi    | ufficioVendite

E vorremmo ottenere:

utente     | listaGruppi
----------------------------------------
m.rossi    | admin;ufficioIT
a.bianchi  | ufficioAmministrazione
p.verdi    | segreteria;ufficioVendite

Questo risultato si può ottenere utilizzando la funzione STUFF:

SELECT DISTINCT
        sUte.utente,
        ISNULL(STUFF(( SELECT DISTINCT ';' + sUtGp.gruppo
                       FROM     sys_gruppiUtente AS sUtGp
                       WHERE    sUtGp.utente = sUte.utente
                     FOR
                       XML PATH('')
                     ), 1, 1, ''), '') AS gruppi
FROM    sys_utenti AS sUte
ORDER BY sUte.utente

La query è leggermente complessa, utilizza una subquery per costruire i valori da restituire che, grazie alla mappatura in XML, produce una tabella ridotta e poi la "impacchetta" con STUFF.

La costruzione della tabella ridotta utilizza la mappatura XML: grazie la clausola FOR XML  -che trasforma la "tabella dei dati" in una struttura XML- e poi specifica PATH('') -che indica a SQL di non usare nessun elemento per racchiudere i dati-.

La funzione STUFF, invece, consente di inserire una stringa in un'altra stringa eliminando un numero di caratteri specificato nella posizione iniziale della prima stringa e inserendo la seconda stringa in tale posizione.

I parametri sono:

• la tabella dei dati di origine
• la posizione da cui iniziare l'elaborazione
• il numero di caratteri da sostituire
• la stringa con cui fare la sostituzione

Nel nostro caso, abbiamo questi valori:

• la tabella "mappata" in xml
• la posizione iniziale della stringa (in SQL, si parte da 1 e non da 0!)
• il numero dei caratteri nella stringa di prefisso (';' è un solo carattere)
• una stringa vuota con cui fare la sostituzione (in questo modo, si fa effettivamente l'eliminazione)

Questo è un sistema flessibile e potente per ricostruire alcune informazioni utili.

Nota:
è importante notare l'inserimento di una stringa di X caratteri per separare i valori, che verrà successivamente rimossa da STUFF per il primo valore.

In pratica, da una stringa ;valoreA;valoreB otterremo valoreA;valoreB.

Nota:
il collegamento tra la subquery usata nella SELECT e la query principale è dato dal WHERE, che collega un campo della subquery ad un campo della query principale.
Questo è uno dei pochi casi (se non l'unico, forse) in cui si possono usare riferimenti esterni allo scope della query in oggetto (nel nostro caso, la subquery).