Comment passer d’une liste de valeurs séparées par des virgules à une table, et inversement ?

Publié le par

La réponse est très courte : avec les fonctionnalités SQLXML et XQuery intégrées à SQL Server.

Il est parfois utile de pouvoir générer une liste de valeurs d’une colonne, séparées par des virgules ou un autre symbole imprimable. Bien que cela serve souvent des buts de présentation, qui n’ont donc rien à faire avec SQL, il se trouve des cas où l’on n’a pas le choix. C’est par exemple le cas avec SQL Server Reporting Services (SSRS) : en effet, cet outil efficace de création assistée de rapports permet de choisir plusieurs valeurs pour un paramètre. SSRS les transmet ensuite sous la forme d’une chaîne de caractères, dont les valeurs sont séparées par des virgules. Lorsqu’on requête les données avec un tel paramètre, il faut dépouiller la chaîne pour en obtenir les valeurs scalaires, intelligibles par le moteur de bases de données.

Voyons un premier exemple très simple de génération de liste de valeurs séparées par des virgules :

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
CREATE TABLE dbo.liste_GUID
(
        valeur_scalaire uniqueidentifier
)
GO

INSERT INTO dbo.liste_GUID (valeur_scalaire)
VALUES ('30815903-5911-418B-BB56-CE0CF4981142')
        , ('029FE2E3-E57D-4C4D-A514-2448149560F9')
        , ('F6A73DFA-C79A-40B5-B12F-55DA66C5A158')
        , ('D896FB47-B5B8-4E8F-BFA1-62A65F66CB8B')
GO

DECLARE @s varchar(max);

WITH
        CUMUL (csv) AS
        (
                SELECT CAST(valeur_scalaire AS char(36)) +  ','
                FROM   dbo.liste_GUID
                FOR    XML PATH('')
        )
SELECT  @s = csv
FROM    CUMUL

SELECT @s

Notons qu’avec cet exemple, nous avons une virgule en toute fin de chaîne. On peut l’éliminer simplement en écrivant :

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @s varchar(max);

WITH
        CUMUL (csv) AS
        (
                SELECT CAST(valeur_scalaire AS char(36)) +  ','
                FROM   dbo.liste_GUID
                FOR    XML PATH('')
        )
SELECT  @s = LEFT(csv, LEN(csv) - 1)
FROM    CUMUL

SELECT @s

Si l’on a besoin de réaliser cela pour un ensemble (i.e. une colonne), on utilisera l’opérateur APPLY, ou une sous requête corrélée.
Pour l’exemple, considérons les tables suivantes :

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
60
61
62
63
CREATE TABLE Contact
(
        IDContact int IDENTITY
                CONSTRAINT PK_Contact PRIMARY KEY
        , NomContact varchar(20) NOT NULL
                CONSTRAINT UQ_Contact__NomContact UNIQUE
)
GO
 
CREATE TABLE Club
(
        IDClub int IDENTITY
                CONSTRAINT PK_Club PRIMARY KEY
        , NomClub varchar(20) NOT NULL
                CONSTRAINT UQ_Club__NomClub UNIQUE
)
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

On obtient alors la liste des clubs desquels un contact est membre avec l’une des requêtes suivantes :

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
-- Avec l'opérateur APPLY
SELECT          CT.NomContact
                , LEFT(LC.liste_club, LEN(LC.liste_club) - 1) AS liste_club
FROM            dbo.Contact AS CT
CROSS APPLY     (
                        SELECT          CL.NomClub + ', '
                        FROM            dbo.Club AS CL
                        INNER JOIN      dbo.Membre AS MB
                                                ON MB.IDClub = CL.IDClub
                        WHERE           CT.IDContact = MB.IDContact
                        FOR             XML PATH('')
                ) AS LC(liste_club);

-- Avec une sous-requête corrélée
-- Pour éliminer la virgule en fin de chaîne, on utilise ici une expression de table commune (CTE)
;WITH
        CTE AS
        (
                SELECT  CT.NomContact
                        , (
                                SELECT          CL.NomClub + ', '
                                FROM            dbo.Club AS CL
                                INNER JOIN      dbo.Membre AS MB
                                                        ON MB.IDClub = CL.IDClub
                                WHERE           CT.IDContact = MB.IDContact
                                FOR             XML PATH('')
                        ) AS liste_club
                FROM    dbo.Contact AS CT
        )
SELECT  NomContact
        , LEFT(liste_club, LEN(liste_club) - 1) AS liste_club
FROM    CTE;

-- Mais on peut se passer de la CTE en utilisant une table dérivée :
SELECT  LC.NomContact
        , LEFT(LC.liste_club, LEN(LC.liste_club) - 1) AS liste_club
FROM    (
                SELECT  CT.NomContact
                        , (
                                SELECT          CL.NomClub + ', '
                                FROM            dbo.Club AS CL
                                INNER JOIN      dbo.Membre AS MB
                                                        ON MB.IDClub = CL.IDClub
                                WHERE           CT.IDContact = MB.IDContact
                                FOR             XML PATH('')
                        ) AS liste_club
                FROM    dbo.Contact AS CT
        ) AS LC(NomContact, liste_club)

Maintenant, supposons que nous avons un rapport SSRS qui appelle une procédure stockée, et que l’un des paramètres du rapport permet un choix multiple de valeurs. Il nous est donc nécessaire de transformer cette liste de valeurs en une table d’une seule colonne :

1
2
3
4
5
6
7
8
9
10
DECLARE @s varchar(max) = '30815903-5911-418B-BB56-CE0CF4981142,029FE2E3-E57D-4C4D-A514-2448149560F9,F6A73DFA-C79A-40B5-B12F-55DA66C5A158,D896FB47-B5B8-4E8F-BFA1-62A65F66CB8B'

-- On ajoute la balise v en tête et en queue de chaîne,
-- et on remplace les virgules par le doublet
DECLARE @x xml  = CAST('<v>'+ REPLACE(@s, ',' ,'</v><v>') + '</v>' AS  xml)

-- On dépouille le document XML créé en utilisant la fonction nodes()
-- du langage XQuery, intégré à SQL Server depuis sa version 2005
SELECT vs.value('.', 'uniqueidentifier') AS valeur_scalaire
FROM   @x.nodes('/v') AS x(vs)

L’utilisation de FOR XML PATH est de très loin la méthode qui permet de générer une liste de valeurs de façon performante. Ceci n’en fait pas une raison pour l’utiliser à tors et à travers, mais seulement lorsque cela est nécessaire.

ElSüket.

Laisser un commentaire