Returning many-to-one composite in SQL in one row

Question

I have the following tables

User
---------------------------
UserID, FirstName, LastName


Role
---------------------------
RoleID, UserID, RoleName

Assuming a many to one relationship between Role and User what is the best way I can return in one query a user along with all the roles they might be in, in a single row?

Previously I have always loaded the Roles (or similar data) in a separate query but the problem is that if your data grows this technique becomes too slow.

See http://stackoverflow.com/questions/3389347/ssrs-group-concat-equivalent-using-an-expression — The Scrum Meister, Aug 18 '11 at 07:55

score 1 · Answer 1 · answered Aug 18 '11 at 08:17

Getting your data into a single row with more columns isn't inherently more efficient than returning multiple rows with fewer columns. Creating and destroying the connection is probably the most expensive operation your doing, so you might consider creating a stored procedure that wraps both queries and then accessing the results using NextResult

score 1 · Accepted Answer · answered Aug 18 '11 at 09:33

This should give you a single row per user in the following format

Single user, multiple roles

WITH cte (userId, roleList, roleNameTemp, level)
as 
(
    SELECT  userId
            , CAST( '' AS VARCHAR(max) )
            , CAST( '' AS VARCHAR(200) )
            , 0 level
    FROM    Role
    GROUP BY UserId 
    UNION ALL
    SELECT  r.userId
            , roleList + 
                CASE WHEN level = 0 THEN '' ELSE ', ' END + RoleName 
            , CAST( RoleName AS VARCHAR(200))
            , level + 1
    FROM CTE c
    INNER JOIN Role r ON c.userId = r.userid
    WHERE   r.RoleName  > c.roleNameTemp 
)

SELECT      UserId
            ,FirstName
            ,LastName
            ,roleList 
FROM 
(
    SELECT      u.UserId
                ,u.FirstName
                ,u.LastName
                ,c.roleList 
                ,ROW_NUMBER() OVER (Partition by c.userid order by level desc) rowNumber
    FROM        cte c
    INNER JOIN  [User] u on c.userId = u.UserId
)   r
WHERE rownumber = 1

Returning many-to-one composite in SQL in one row

2 Answers2