SQL Server: Loading all the values from a table into a single line

It’s a common requirement to load all the values from a table into a single line for consumption. For example, you might retrieve a User record, but you want the list of roles that the user belongs to in a single comma separated line. The usual way of performing this was to create an inline function, pass it the UserId of the record and have it build the list. The problem with that is the inline function – it causes a table scan, which is highly undesirable.

Today, I learnt another trick (again from John Simon). Basically, you can inline load the strings using the STUFF statement and an XPATH subquery, as follows:

SELECT UserId,RolesList=STUFF((

   SELECT ‘,’ + Role.Name

   FROM dbo.Roles AS Role

   INNER JOIN dbo.UserRoles userRole

   ON Role.RoleId=userRole.RoleId

   WHERE userRole.UserId = users.UserId

   ORDER BY ‘,’ + Role.Name

   FOR XML PATH()

), 1, 1, )

FROM dbo.Users AS users

This outputs something like the following:

1,”CustomerAdmin,RegularUser”

2,”SiteAdmin,CustomerAdmin,BusinessUser,RegularUser”

3,”BusinessUser”

4,”CustomerAdmin,BusinessUser”

One Response to SQL Server: Loading all the values from a table into a single line

  1. […] February 7, 2011 by Brian McKay Leave a Comment It's a common requirement to load all the values from a table into a single line for consumption. For example, you might retrieve a User record, but you want the list of roles that the user belongs to in a single comma separated line. The usual way of performing this was to create an inline function, pass it the UserId of the record and have it build the list. The problem with that is the inline function – it causes a table scan, which is highly … Read More […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: