Have you ever wanted to concatenate a repeating column as a comma delimitated string in the same query result? Normally, I would use a Scalar Valued Function to COALESCE the values and return them to the calling query (as in the recent post here by Dustin Brisson), but there is a way to do this all in a single request.

SELECT      DISTINCT
         UserID,

         STUFF(
               (SELECT      ',' + SubTableUser.UserRole
               FROM      TableUser AS SubTableUser
               WHERE      SubTableUser.UserID = TableUser.UserID
               FOR XML PATH('')), 1, 1, '') AS UserRole

FROM      TableUser
ORDER BY   UserID

Above is a query with a sub-query. The parent query using DISTINCT against the record values that will remain the same during the repeated result set. The result set in this case happens because there are several users in this fictional database that have more than one user role assigned.

The sub-query, concatenates a comma in front of each additional result for the user role obtained when the user ID of the sub-query matches the distinct user ID of the parent query. This results in a very nice string with an awful leading comma.

You could strip out the comma whatever will display the results, but here we are using the STUFF command to take the first character, in this case a comma, and replace it with nothing. This results in a nice clean list of user roles for each distinct user ID.

If you find this post useful please leave a comment and let me know how you used the information.