While working on a project recently I ran into an instance where I was creating a view in T-SQL that would need to select multiple results from a subselect and then put them into a single column within the view for use within a data grid. This needed to be done in order to maximize readability and ease of use.
After spending a lot of time searching and investigating on the internet I found out that there was actually a very easy way to turn a sub select into a single column for use in a view. As it turns out that there is a simple XML trick that does exactly what I needed of pulling multiple rows from a subselect into a single column.
Similar to my situation, one instance where this may come in handy is if you wanted to create a view of all of your application’s membership users and then in the result display in one column all the roles they are in.
SELECT U.UserId, U.UserName, U.Email, U.LastLoginDate, STUFF ((SELECT ', ' + LTRIM(RTRIM(RoleName)) FROM dbo.vw_aspnet_UsersInRoles UR INNER JOIN dbo.vw_aspnet_Roles R ON R.RoleId = UR.RoleId WHERE UR.UserId = U.UserId FOR XML PATH('')), 1, 2, '') AS [Roles] FROM dbo.vw_aspnet_MembershipUsers U
You should be able to run the above query against any database that has the SQL Server membership tables and views installed to see the result. The main part of this query that is important to note is this section:
STUFF ((SELECT ', ' + LTRIM(RTRIM(RoleName)) FROM dbo.vw_aspnet_UsersInRoles UR INNER JOIN dbo.vw_aspnet_Roles R ON R.RoleId = UR.RoleId WHERE UR.UserId = U.UserId FOR XML PATH('')), 1, 2, '')
What this does is the STUFF method returns a character string from the results of the query. The numeric values at the end are for cutting off the first part of the select statement. It removes the preceding comma and space before the first role. All other roles end up being comma separated as part of the select statement. If comma separation doesn’t work for you then you can change the first part of that query as well as the parameters of the stuff command.
This worked great for me and gave me exactly the results I was looking for.