CODEWITHSUNDEEP
sqlsql-server
happenz
happenz

Reputation: 43

sql server Concatenate Multiple Rows Using FOR XML PATH

I'm having a bit of trouble implementing the XML PATH method of concatenating multiple rows. So, given the following sql-table, temptable

user | userid | email    |   group

a      usera    [email protected]     admin
a      usera    [email protected]     test
a      usera    [email protected]     edit
b      userb    [email protected]     read
b      userb    [email protected]     write
c      userc    [email protected]     read
c      userc    [email protected]     write

i receive with this code the following:

SELECT a.[user],

STUFF((SELECT  ', ' +  [group] [text()]
      FROM [temptable] 
      WHERE [user] = a.[user]
      for XML PATH (''),TYPE).
      value('.','NVARCHAR(MAX)'),1,2,'') AS [group]

FROM [temptable] as a
GROUP BY a.[user]

result:

user |   group

a      admin,test,edit
b      read,write
c      read,write

but i need this

    user | userid | email    |   group

    a      usera    [email protected]     admin,test,edit
    b      userb    [email protected]     read,write
    c      userc    [email protected]     read,write

Upvotes: 1

Views: 7320

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Try below

SELECT a.[user],a.userid,a.email
STUFF((SELECT  ', ' +  [group] [text()]
      FROM [temptable] b on a.[user]=b.[user]
      for XML PATH (''),TYPE).
      value('.','NVARCHAR(MAX)'),1,2,'') AS [group]
FROM [temptable] as a
GROUP BY a.[user],a.userid,a.email

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Just include other column also in group by clause :

SELECT a.[user], userid, email,
       STUFF((SELECT  ', ' +  [group] [text()]
              FROM [temptable] 
              WHERE [user] = a.[user]
              FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)'),1,2,''
            ) AS [group]
FROM [temptable] AS a
GROUP BY a.[user], userid, email;

However, DISTINCT would also solve your problem :

SELECT DISTINCT a.[user], userid, email,
           STUFF((SELECT  ', ' +  [group] [text()]
                  FROM [temptable] 
                  WHERE [user] = a.[user]
                  FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)'),1,2,''
                ) AS [group]
FROM [temptable] AS a;

Upvotes: 0

Related Questions