Il est souvent demandé sur le forum comment on peut créer une liste de valeurs stockées dans une colonne en une liste.
Cela est en effet souvent demandé par le client pour des rapports, mais il n’est pas évident de prime abord de fournir un tel résultat, puisqu’il est anti-relationnel !
Prenons pour exemple des adhérents à plusieurs clubs :
NomContact NomClub --------------- ------------ BipBip SQLServer BipBip Oracle BipBip PostGreSQL Coyotte MySQL Coyotte FireBird Titi Sybase Sylvestre DB2 Sylvestre SQLite Bugs Bunny SQLServer Bugs Bunny PostGreSQL Daffy Duck FireBird Daffy Duck DB2 Sam Le Pirate SQLite
Le résultat recherché se présente sous la forme :
NomContact ListeClub --------------- ----------------------------- Titi Sybase Sam Le Pirate SQLite Daffy Duck FireBird, DB2 Bugs Bunny SQLServer, PostGreSQL Sylvestre DB2, SQLite Coyotte MySQL, FireBird BipBip SQLServer, Oracle, PostGreSQL
Représentons physiquement nos clubs et leurs adhérents :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | --------------------------------- -- Nicolas SOUQUET - 04/03/2009 - --------------------------------- CREATE TABLE Contact ( IDContact INT IDENTITY CONSTRAINT PK_Contact PRIMARY KEY, NomContact VARCHAR(20) NOT NULL ) GO CREATE TABLE Club ( IDClub INT IDENTITY CONSTRAINT PK_Club PRIMARY KEY, NomClub VARCHAR(20) NOT NULL ) GO CREATE TABLE Membre ( IDMembre INT IDENTITY CONSTRAINT PK_Membre PRIMARY KEY, IDContact INT NOT NULL CONSTRAINT FK_Membre_IDContact FOREIGN KEY(IDContact) REFERENCES Contact(IDContact), IDClub INT NOT NULL CONSTRAINT FK_Membre_IDClub FOREIGN KEY(IDClub) REFERENCES Club(IDClub), CONSTRAINT UQ_Membre_IDContact_IDClub UNIQUE (IDContact, IDClub) ) GO INSERT INTO dbo.Contact (NomContact) VALUES('BipBip') INSERT INTO dbo.Contact (NomContact) VALUES('Coyotte') INSERT INTO dbo.Contact (NomContact) VALUES('Titi') INSERT INTO dbo.Contact (NomContact) VALUES('Sylvestre') INSERT INTO dbo.Contact (NomContact) VALUES('Bugs Bunny') INSERT INTO dbo.Contact (NomContact) VALUES('Daffy Duck') INSERT INTO dbo.Contact (NomContact) VALUES('Sam Le Pirate') GO INSERT INTO dbo.Club (NomClub) VALUES ('SQLServer') INSERT INTO dbo.Club (NomClub) VALUES ('Oracle') INSERT INTO dbo.Club (NomClub) VALUES ('PostGreSQL') INSERT INTO dbo.Club (NomClub) VALUES ('MySQL') INSERT INTO dbo.Club (NomClub) VALUES ('FireBird') INSERT INTO dbo.Club (NomClub) VALUES ('Sybase') INSERT INTO dbo.Club (NomClub) VALUES ('DB2') INSERT INTO dbo.Club (NomClub) VALUES ('SQLite') GO INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (1, 1) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (1, 2) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (1, 3) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (2, 4) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (2, 5) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (3, 6) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (4, 7) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (4, 8) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (5, 1) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (5, 3) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (6, 5) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (6, 7) INSERT INTO dbo.Membre (IDContact, IDClub) VALUES (7, 8) GO |
La requête qui permet d’obtenir tous les clubs auxquels participent tous nos contacts est alors simple :
1 2 3 4 5 | SELECT CONTACT.NomContact, CLUB.NomClub FROM dbo.Contact CONTACT JOIN dbo.Membre MEMBRE ON MBR.IDContact = CONTACT.IDContact JOIN dbo.Club CLUB ON CLUB.IDClub = MEMBRE.IDClub |
En revanche il est plus complexe d’agréger toutes ces valeurs pour en fournir une liste par contact …
Voyons comment on peut faire pour un seul membre :
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @nomContact VARCHAR(20), @listeClubs VARCHAR(1024) SELECT @nomContact = 'BipBip' SELECT @listeClubs = ISNULL(@listeClubs, '') + CLUB.NomClub + ', ' FROM dbo.Contact CONTACT JOIN dbo.Membre MEMBRE ON MEMBRE.IDContact = CONTACT.IDContact JOIN dbo.Club CLUB ON CLUB.IDClub = MEMBRE.IDClub WHERE CONTACT.NomContact = @nomContact SELECT @nomContact AS Contact, LEFT(@listeClubs, LEN(@listeClubs) - 1) AS ListeClubs |
Nous permet d’obtenir :
Contact ListeClubs ------- ----------------------------- BipBip SQLServer, Oracle, PostGreSQL
Dès lors pour exécuter cette même requête pour tous les contacts, nous allons avoir (encore une fois diront certains) à la puissance des expressions de table commune récursives, introduites avec SQL Server 2005 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | --------------------------------- -- Nicolas SOUQUET - 04/03/2009 - --------------------------------- WITH -- Extrait les informations pour chaque contact suivant la requête "normale" -- en attribuant à chaque tuple un indice arbitraire CTE_LISTE_BRUTE(IDContact, NomContact, NomClub, Indice) AS ( SELECT CONTACT.IDContact, CONTACT.NomContact, CLUB.NomClub, ROW_NUMBER() OVER(PARTITION BY CONTACT.IDContact ORDER BY CONTACT.IDContact) AS Indice FROM dbo.Contact CONTACT JOIN dbo.Membre MEMBRE ON MEMBRE.IDContact = CONTACT.IDContact JOIN dbo.Club CLUB ON CLUB.IDClub = MEMBRE.IDClub ), -- Aggrège les informations pour chaque membre d'un club CTE_LISTE_CLUB(IDContact, NomContact, ListeClub, Compteur) AS ( -- Recherche tous les contacts qui sont membres d'au moins un club SELECT IDContact, NomContact, CAST(NomClub AS VARCHAR(1024)) AS ListeClub, 1 AS Compteur FROM CTE_LISTE_BRUTE WHERE Indice = 1 UNION ALL -- Recherche les autres clubs adhérés par le même membre, pour chaque membre SELECT CTE_LISTE_CLUB.IDContact, CTE_LISTE_CLUB.NomContact, CAST(CTE_LISTE_CLUB.ListeClub + ', ' + CTE_LISTE_BRUTE.NomClub AS VARCHAR(1024)) AS ListeClub, CTE_LISTE_CLUB.Compteur + 1 FROM CTE_LISTE_CLUB JOIN CTE_LISTE_BRUTE ON CTE_LISTE_CLUB.IDContact = CTE_LISTE_BRUTE.IDContact WHERE Indice = Compteur + 1 ), -- Recherche le nombre total d'adhésions pour chaque membre CTE_MAX(IDContact, maxCompteur) AS ( SELECT IDContact, MAX(Compteur) AS maxCompteur FROM CTE_LISTE_CLUB GROUP BY IDContact ) SELECT CTE_LISTE_CLUB.NomContact, CTE_LISTE_CLUB.ListeClub FROM CTE_LISTE_CLUB JOIN CTE_MAX ON CTE_LISTE_CLUB.Compteur = CTE_MAX.maxCompteur AND CTE_LISTE_CLUB.IDContact = CTE_MAX.IDContact |
Nous obtenons ainsi :
NomContact ListeClub --------------- ----------------------------- Titi Sybase Sam Le Pirate SQLite Daffy Duck FireBird, DB2 Bugs Bunny SQLServer, PostGreSQL Sylvestre DB2, SQLite Coyotte MySQL, FireBird BipBip SQLServer, Oracle, PostGreSQL
ElSuket
Je suis entièrement d’accord avec toi, mais cela est par exemple difficile réalisable pour un rapport sous Reporting Services
Heu… J’ai un peu envie de remarquer qu’il s’agit plus là d’un traitement de résultats que d’une requête en elle-même…
Ma solution d’approcher plus de :
faire la requête
2
3
4
5
6
7
SELECT CONTACT.NomContact, <br />
CLUB.NomClub <br />
FROM dbo.Contact CONTACT <br />
JOIN dbo.Membre MEMBRE ON MBR.IDContact = CONTACT.IDContact <br />
JOIN dbo.Club CLUB ON CLUB.IDClub = MEMBRE.IDClub <br />
ORDER BY CONTACT.NomContact <br />
puis le traitement suivant dans un langage autre (celui utiliser côté applicatif) [simili-PHP]:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$last_contact = null; <br />
echo BEGINING_OF_LIST; <br />
for({$contact, $club} = next_result()) <br />
if($contact == $last_contact) <br />
echo ', '.$club; <br />
else <br />
{ <br />
if(null != $last_contact) <br />
echo END_OF_LINE; <br />
echo BEGINING_OF_CONTACT; <br />
echo $club; <br />
$last_contact = $contact; <br />
} <br />
echo END_OF_CONTACT; <br />
echo END_OF_LIST; <br />
Les avantages que je vois : on se surchage pas le serveur de BDD par du traitement applicatif pour le quel il n’est pas fait, si l’application tourne sur le poste du client, alors on n’a même pas à fournir la puissance de calcul nécessaire à l’application !
Si le client souhaite modifier le format de présentation par la suite, alors il n’y a que le code applicatif à modifier (au lieu des deux, ce qui peut être plus pénible pour une BDD conséquente)…
Après ça n’engage que moi ^^