Sql select to string

Question

J'ai une requête qui renvoie un ensemble de résultats d'une colonne, mais je veux qu'elle soit convertie en une longue chaîne pour être utilisée comme sous-requête. Je veux pouvoir le faire :

SELECT user.*, (SELECT group_name FROM user_groups WHERE userID = user.ID) AS Groups FROM user

Cependant, cela échouera car un utilisateur peut appartenir à plus d'un groupe. Par exemple, si l'utilisateur appartient à {"writer", "editor"} Je veux qu'il renvoie une chaîne de caractères comme ceci : "writer, editor" .

Comment puis-je le faire ?

Answer 1

Vous pouvez utiliser FOR XML pour effectuer cette action de pivotement. Voici un exemple fonctionnel.

set nocount on

declare @Users Table
(
    UserId  int,
    UserName varchar (20)
)

declare @UserGroups Table
(
GroupId int,
UserId int,
GroupName varchar (20)
)

Insert @Users Values (1, 'Tim')
Insert @Users Values (2, 'Jane')
Insert @Users Values (3, 'Hal')

Insert @UserGroups Values (1, 1, 'Admin')
Insert @UserGroups Values (2, 1, 'Power-users')
Insert @UserGroups Values (3, 2, 'Noobs')
Insert @UserGroups Values (4, 2, 'Users')
Insert @UserGroups Values (5, 3, 'Noobs')

/* How this works */

SELECT 'FirstStep  : Users table'
SELECT * FROM @Users

SELECT 'NextStep : User Groups table'
SELECT * FROM @UserGroups

SELECT 'NextStep : Users & UserGroups table'
SELECT * 
FROM @Users U
    INNER JOIN @UserGroups UG ON U.UserId = UG.UserId

SELECT 'NextStep : Just get the groups for one user (UserId = 2)'
SELECT GroupName
FROM @UserGroups UG
WHERE UG.userID = 2
ORDER BY GroupName

SELECT 'NextStep : When you use XML Path the output comes out in XML format'
SELECT GroupName
FROM @UserGroups UG
WHERE UG.userID = 2
ORDER BY GroupName
FOR XML PATH('') -- XML Path added

SELECT 'NextStep : When you remove the column name the XML tags go away, 
but it looks ugly because there is no separator' 
SELECT GroupName + '' -- Added an empty string to remove the column name
FROM @UserGroups UG
WHERE UG.userID = 2
ORDER BY GroupName
FOR XML PATH('') 

SELECT 'NextStep : Add a separator
We add it to the beginning instead of the end so that we can STUFF later on.' 
SELECT ', ' + GroupName -- Added an empty string to remove the column name
FROM @UserGroups UG
WHERE UG.userID = 2
ORDER BY GroupName
FOR XML PATH('') 

SELECT 'NextStep : I don''t like that ugly XML column name. Let me give it my own name' 
SELECT 
(
    SELECT ', ' + GroupName
    FROM @UserGroups UG
    WHERE UG.userID = 2
    ORDER BY GroupName
    FOR XML PATH('') 
) as UserGroups

SELECT 'NextStep : STUFF the extra comma' 
SELECT STUFF 
    (
        (
            SELECT ', ' + GroupName
            FROM @UserGroups UG
            WHERE UG.userID = 2
            ORDER BY GroupName
            FOR XML PATH('') 
        ),
        1, 2, ''
    ) as UserGroups

SELECT 'NextStep : Now join it with the Users table by the UserId and get the UserName' 
SELECT 
    U.UserName, 
    STUFF 
    (
        (
            SELECT ', ' + GroupName
            FROM @UserGroups UG
            WHERE UG.userID = U.userID
            ORDER BY GroupName
            FOR XML PATH('') 
        ),
        1, 2, ''
    ) as UserGroups
FROM @Users U