sql server Concatenate Multiple Rows Using FOR XML PATH

Question

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

just edited you sql script

Sreenu131
– Sreenu131

2018-10-29 10:56:18 +00:00
Commented Oct 29, 2018 at 10:56 — Sreenu131
– Sreenu131, Commented Oct 29, 2018 at 10:56

Fahmi · Accepted Answer · 2018-10-29 10:12:17Z

1

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

answered Oct 29, 2018 at 10:12

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-10-29 10:11:54Z

0

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;

answered Oct 29, 2018 at 10:11

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

sql server Concatenate Multiple Rows Using FOR XML PATH

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related