Fino alla versione 2000 per esplodere una gerarchia padre-figlio erano necessarie tabelle di appoggio, viste e stored procedures costruite ad-hoc.
Con SqlServer 2005 è stata introdotta una nuova istruzione che ci permette di raggiungere lo scopo in una singola query.
Vediamo in dettaglio la problematica ed il funzionamento della parola chiave WITH.
Supponiamo di avere una tabella che contiene una struttura Padre-Figlio e un po' di dati.
[code language="SQL"]
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE [dbo].[Tree]
(
[Id] [int] NOT NULL,
[Padre] [int] NULL,
[Descrizione] [varchar](100) NULL,
CONSTRAINT [PK_Tree] PRIMARY KEY CLUSTERED
([Id] ASC)
)
GO
INSERT INTO Tree(Id, Padre, Descrizione) Values (1,Null,'Padre')
INSERT INTO Tree(Id, Padre, Descrizione) Values (2,1,'Figlio 1')
INSERT INTO Tree(Id, Padre, Descrizione) Values (3,1,'Figlio 2')
INSERT INTO Tree(Id, Padre, Descrizione) Values (4,1,'Figlio 3')
INSERT INTO Tree(Id, Padre, Descrizione) Values (5,2,'Nipote 1')
INSERT INTO Tree(Id, Padre, Descrizione) Values (6,2,'Nipote 2')
INSERT INTO Tree(Id, Padre, Descrizione) Values (7,3,'Nipote 3')
INSERT INTO Tree(Id, Padre, Descrizione) Values (8,7,'Pronipote 1')
GO
[/code]
Quello che vogliamo ottenere è l'elenco dei nodi dell'albero con l'indicazione del livello del nodo e della struttura padre-figlio di tutta la catena in un'unica query.
Questi sono i dati che abbiamo inserito:
Id Padre Descrizione
----------- ----------- ---------------------
1 NULL Padre
2 1 Figlio 1
3 1 Figlio 2
4 1 Figlio 3
5 2 Nipote 1
6 2 Nipote 2
7 3 Nipote 3
8 7 Pronipote 1
Per prima cosa dobbiamo individuare il nodo radice.
[code language="SQL"]
SELECT Id,Padre,Descrizione
FROM Tree
WHERE Padre is Null
[/code]
Utilizziamo With per iniziare a dichiarare la vista che ci permetterà di esplodere la gerarchia. Dobbiamo indicare nella dichiarazione della vista con With l'elenco delle colonne che utilizzeremo come risultato.
[code language="SQL"]
WITH TreeExploded(Id,Padre,Descrizione,Struttura,Livello) AS
(
SELECT Id,Padre,CAST(Descrizione as varchar(50)), CAST('' as varchar(20)), 0
FROM Tree
WHERE Padre is Null
)
SELECT * FROM TreeExploded
[/code]
Abbiamo dichiarato 2 colonne che attualmente non abbiamo, ovvero "Struttura" e "Livello". Queste colonne verranno calcolate nella vista. Con WITH con dichiaro la vista che poi posso riutilizzare nella select successiva.
Nella query all'interno della vista abbiamo inoltre fatto un CAST di una stringa vuota a varchar(100), questo è necessario per indicare a WITH con quale tipo di dato avrà a che fare con le successive righe.
Fino a qui non abbiamo visto nulla di strano, se non il modo con cui si può dichiarare una vista subito prima di utilizzarla. La caratteristica di WITH però è la possibilità di utilizzare i dati estratti dalla vista, come dati di partenza per la vista stessa. Ovvero possiamo referenziare all'interno della vista, la vista stessa in una query in UNION ALL. Vediamo come fare:
[code language="SQL"]
WITH TreeExploded(Id,Padre,Descrizione,Struttura,Livello) AS
(
SELECT Id,
Padre,
CAST(Descrizione as varchar(50)),
CAST('' as varchar(20)), 0
FROM Tree
WHERE Padre is Null
UNION ALL
SELECT Tree.Id,
Tree.Padre,
CAST(Tree.Descrizione as varchar(50)),
CAST('' as varchar(20)),
Livello + 1
FROM Tree
JOIN TreeExploded ON
Tree.Padre = TreeExploded.Id
)
SELECT * FROM TreeExploded
[/code]
Nella seconda query all'interno della WITH abbiamo messo in JOIN la vista stessa. In questo modo possiamo utilizzare i dati estratti dalla prima query nella UNION per estrarre ulteriori dati nella query successiva senza fare ricorso a tabelle temporanee.
Arricchiamo il tutto con la definizione della struttura e con un po' di ordinamenti.
[code language="SQL"]
WITH TreeExploded(Id,Padre,Descrizione,Struttura,Livello) AS
(
SELECT Id,
Padre,
CAST(Descrizione as varchar(50)),
CAST(Id as varchar(20)), 0
FROM Tree
WHERE Padre is Null
UNION ALL
SELECT Tree.Id,
Tree.Padre,
CAST(REPLICATE ( ' ' , Livello ) + Tree.Descrizione as varchar(50)),
CAST(Struttura + '.' + CAST(Tree.Id as varchar(10)) as varchar(20)),
Livello + 1
FROM Tree
JOIN TreeExploded ON
Tree.Padre = TreeExploded.Id
)
SELECT Id,Padre,Struttura,Descrizione,Livello
FROM TreeExploded
ORDER By Struttura
[/code]
Ho introdotto il REPLICATE per indentare anche le descrizioni in modo da rendere più chiaro il risultato:
Id Padre Struttura Descrizione Livello
----------- ----------- -------------------- -------------------------------------------------- -----------
1 NULL 1 Padre 0
2 1 1.2 Figlio 1 1
5 2 1.2.5 Nipote 1 2
6 2 1.2.6 Nipote 2 2
3 1 1.3 Figlio 2 1
7 3 1.3.7 Nipote 3 2
8 7 1.3.7.8 Pronipote 1 3
4 1 1.4 Figlio 3 1
Buon lavoro.