STUFF & XML PATH unable to group.

Question

Please Check the below URL : https://rextester.com/IYN29111 From that URL, I'm unable to get the comid as one time. Please solve it. Thank you.

Jayesh Vaghasiya · Accepted Answer

Try This    create table #tmp(roleid int,comid varchar) insert into #tmp values(1,'A'),(1,'B'),(2,'C'),(2,'D') SELECT roleid, comid= STUFF((SELECT ', ' + comid FROM #tmp TM where TM.roleid = TT.roleid FOR XML PATH('')), 1, 2, '') FROM #tmp TT GROUP BY roleid drop table #tmp

Suraj Kumar · Answer

We use join based on id to get the other column such as name or something other with full list from the table. You can learn in details about STUFF and XML path in sql server here-   https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server    https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

Sreekanth Reddy · Answer

Hi Jayesh, Can you explain " TM.roleid = TT.roleid "  in Your query.    What makes you to think about it ?    Thank you