Loading

Stuff with XML PATH

Stuff with XML PATH

Sai Reddy

Sai Reddy

8y1k

id Email

1 [email protected]

1 [email protected]

2 [email protected]

3 [email protected]

3 [email protected]

output:

id email

1 [email protected],[email protected]

2 [email protected]

3 [email protected],[email protected]

I found the following query some where can you explain it?

SELECT id, Email =

STUFF((SELECT ', ' + Email

FROM comma b

WHERE b.id = a.id

FOR XML PATH('')), 1, 1, '')

FROM comma a

GROUP BY id

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
1. SELECT id
2. , Email = STUFF( ( SELECT ', ' + Email
3. FROM comma b
4. WHERE b.id = a.id
5. FOR XML PATH('')
6. ), 1, 1, ''
7. )
8. FROM comma a
9. GROUP BY id
Line 1 : list the id and it is GROUPed BY Line 9
Line 9 : it Groups by id

Line 2: All Emails are grouped by the same id and concated together to form a string of emails

Line 4: it is matching the table from Line 8 to the INNER QUERY between line 2 and 7

Line2: STUFF, it iinsert and string into another string with the help of line 6

https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-2017

Line 5: this does the trick, i allows you to output the result of the elements one by one, so it can be STUFFed and grouped by

THE FINAL QUESTION

Would i use the above code? if i am using SQL 2017 the answer will be NO

why? in SQL 2017 we have

STRING_AGG that does the trick

Your code will look like.....
1. SELECT [id] STRING_AGG (email, ';') AS emails
2. FROM comma
3. GROUP BY [id];
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017