SQL Many Rows To One XML And Stuff

This question arose on the SO forum some months ago.



The poster had a number of rows per UserID and wanted a result set of one row per userId with all the roles that would be associated to them.

The question appears easy, but it does take a bit of critical thinking because any simple join on userId will result in many rows.

My solution was to use the STUFF and For XML trick to work for you.

SELECT 
   U.USER_NAME,
   STUFF((SELECT ',' + UR.ROLE 
          FROM [USER_ROLES] UR
          WHERE UR.USER_ID = U.USER_ID
          FOR XML PATH('')), 1, 1, '') [ROLES]
FROM [USER] U
GROUP BY U.USER_NAME, U.USER_ID
ORDER BY 1

https://rextester.com/YLIJQ30828

Example of compiling a comma seperated list from a single table.

SELECT STUFF((
SELECT ',' + EMPLOYEEID FROM [#TEMP_A]
FOR XML PATH('')), 1, 1, '') as Employees

https://rextester.com/XBUW63425

Ian Fogelman

My name is Ian Fogelman. I like to develop data driven solutions with SQL Server, Python, .NET and predictive analytics.