Stuff with XML PATH

Question

id Email 1 Sai@gmail.com 1 sundeep@gmail.com 2 swaroop@gmail.com 3 Sam@gmail.com 3 Reddy@gmail.com output: id email 1 Sai@gmail.com,sundeep@gmail.com 2 swaroop@gmail.com 3 Sam@gmail.com,Reddy@gmail.com 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

Shahriar Nikkhah · Answer

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 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..... SELECT [id] STRING_AGG (email, ';' ) AS emails FROM comma GROUP BY [id]; https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017