Aggréger des données non numériques d’une entité par une liste de valeurs

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

2 réflexions au sujet de « Aggréger des données non numériques d’une entité par une liste de valeurs »

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

1
2
3
4
5
6
7

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  
ORDER BY CONTACT.NomContact

puis le traitement suivant dans un langage autre (celui utiliser côté applicatif) [simili-PHP]:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

$last_contact = null;  
echo BEGINING_OF_LIST;  
for({$contact, $club} = next_result())  
  if($contact == $last_contact)  
  echo ', '.$club;  
  else  
  {  
  if(null != $last_contact)  
  echo END_OF_LINE;  
  echo BEGINING_OF_CONTACT;  
  echo $club;  
  $last_contact = $contact;  
  }  
echo END_OF_CONTACT;  
echo END_OF_LIST;

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 ^^

Laisser un commentaire Annuler la réponse.

Vous devez être connecté(e) pour rédiger un commentaire.

Identifiez-vous

Créer un compte

Le Blog SQL Server d'ElSüket

Nicolas Souquet – Expert SQL Server

Aggréger des données non numériques d’une entité par une liste de valeurs

2 réflexions au sujet de « Aggréger des données non numériques d’une entité par une liste de valeurs »

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?