0

I'm creating a stored procedure that will return a grouping of details about my database. Part of this SP needs to contain a concatenated list of Groups for a User (a user can belong to many groups). The best practice is to create a temporary table and populate it with group listings as an initial step, then join it up later to return as a large result set for my SP.

I have a table called UserGroup as follows:

CREATE TABLE UserGroup
(
    UserID uniqueidentifier not null,
    GroupID uniqueidentifier not null
)

There's another table, [Group]. It has a GroupName and Description for each GroupID. Then, there's a table called [User] that has a UserID attached to each UserName and user detail columns.

I want to do SOMETHING like this:

INSERT INTO #storegroupdata
(OwnerID, GroupList)
select B.UserID, @temp = COALESCE(@temp + ',', '') + CAST(g.GroupName as nvarchar(200))
from appSec.dbo.UserGroup ug
inner join appSec.dbo.[Group] g on g.GroupID = ug.GroupID
inner join app.dbo.User B on B.UserID = ug.UserID

Obviously, it's psuedocode, because you can't select a variable in the same step you perform data-retrieval (and @temp needs to be cleared after each list is successfully created). But I want to create a temporary table full of concatenated lists...Is this possible without using frowned upon, performance nightmare methods?

The only alternatives I know of are subqueries and cursors. I could have a subquery select the group list for each user -- but this has extremely, extremely harsh performance concerns. I would know, since I have a working version of this that's too slow; it's not a solution. Using a cursor is just about as bad.

Is there a technique I'm not aware of for dealing with a situation like this? Or am I forced to rely on a cursor or subquery to create a list for each user in my database? I also don't want to create any additional procedures or functions to solve this, I'd like one stored procedure to be able to handle everything.

user3042783
  • 41
  • 7
  • 1
    There are a variety of ways to do this. Have you looked here [Simulating group_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Taryn Jul 22 '14 at 13:29
  • Thanks, bluefeet, for the reference -- but my previous implementations all looked something like that and it just wasn't acceptable. – user3042783 Jul 22 '14 at 15:18

1 Answers1

1

in general, you want to avoid cursors. In this case, you can do the aggregation using for xml path:

INSERT INTO #storegroupdata(OwnerID, GroupList)
    select u.userID,
           stuff((select ', ' + CAST(g.GroupName as nvarchar(200))
                  from appSec.dbo.UserGroup ug inner join
                       appSec.dbo.[Group] g
                       on g.GroupID = ug.GroupID
                  where u.UserID = ug.UserID
                  for xml path ('')
                 ), 1, 2, '')
    from app.dbo.User;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the response, this isn't exactly what I was looking for, but it gave me some ideas to try out. I think I might have an answer of my own that I'll post here, if what I'm thinking is correct. – user3042783 Jul 22 '14 at 17:36
  • @user3042783 . . . How is this not what you mean? Your question seems to want the group names concatenated into a comma-delimited list, which this does. – Gordon Linoff Jul 22 '14 at 18:53