Jaylen Jaylen - 2 months ago 7
SQL Question

How to correctly combine multiple rowss into comma seperated string in SQL Server

I am trying to use the

STUFF
function to combine multiple rows of data into a comma separated string.

Here is what I have done

SELECT
s.Id
,s.Name
,STUFF(
(
SELECT
c.Name
FROM [Groups] AS c
INNER JOIN [GroupToUser] AS stc ON stc.CategoryId = c.Id
WHERE stc.StageId = s.Id
FOR XML PATH('')
), 1, 1, '') AS GroupsTheUserBelongsTo
FROM [Users] AS s


This is working somehow like expected. However, it is returning
XML
string missing the first character.

How can I make it return a comma separated string instead of XML?

Answer

You should just need to make sure you put the ',' comma in the query. so ',' + c.Name.

SELECT 
 s.Id
,s.Name
,STUFF(
(
    SELECT

     ',' + c.Name

    FROM [Groups] AS c
    INNER JOIN [GroupToUser] AS stc ON stc.CategoryId = c.Id
    WHERE stc.StageId = s.Id
    FOR XML PATH('')
), 1, 1, '') AS GroupsTheUserBelongsTo 
FROM [Users] AS s

So to explain what is happening for you. you have a standard query that you then tell sql-server to turn into XML but without a root element and because there is no column name it will also be without tags. When you add the comma or other delimiter it will put a comma between each row. STUFF(xmlstring,1,1,'') says take the in the first position of the string take out the character that is there and put in 1 occurrence of '' which basically is the same as removing the leading comma.

here is a little example to break it down into pieces to better understand what is going on.

DECLARE @Table AS TABLE (Col VARCHAR(10))
INSERT INTO @Table VALUES ('A'),('B'),('C')

DECLARE @Values AS VARCHAR(MAX)

SELECT @Values = (
SELECT
    ',' + Col
FROM
    @Table
FOR XML PATH (''))

SELECT @Values

SELECT STUFF(@Values,1,1,'')