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.
Example of compiling a comma seperated list from a single table.