For illustration, suppose we have UserGroup & Users. We want to list the users belonging to each group.
If we do normal joining of the 2 tables, it will look like:
If we combine the values on the rows into a column, it will look like:
Here is the query to do so:
-- Create temporary table to store UserGroup SELECT GroupID = 1, UserGroup = 'Group 1' INTO #UserGroup INSERT INTO #UserGroup VALUES ( 2, 'Group 2' ) INSERT INTO #UserGroup VALUES ( 3, 'Group 3' ) -- Create temporary table to store Users SELECT UserID = 'Superman', GroupID = 1 INTO #Users INSERT INTO #Users VALUES ( 'Luck', 2 ) INSERT INTO #Users VALUES ( 'Himura', 1 ) INSERT INTO #Users VALUES ( 'Selvi', 1 ) INSERT INTO #Users VALUES ( 'Smarty', 2 ) INSERT INTO #Users VALUES ( 'Lucky', 3 ) -- Normal Join SELECT a.UserGroup, b.UserID FROM #UserGroup a LEFT JOIN #Users b ON b.GroupID = a.GroupID ORDER BY a.UserGroup -- Combine Rows into Column SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a WHERE a.GroupID = b.GroupID ORDER BY a.UserID FOR XML PATH('') ) , 1, 2, '') FROM #UserGroup b ORDER BY UserGroup -- This is the key query to combine rows into column SELECT UserID + ', ' FROM #Users WHERE GroupID = 1 FOR XML PATH('')
Update: Looks like I posted something similar quite some time ago, but using different method: Selecting several rows into one row in SQL