How to build this query

Hi,
Please Help me to build this query.

I have got a "User" Table
-------
UserID UserName
-------

1 Tuffy

Another Table "Groups" Table
-------
GroupID GroupName
-------
1 Manager
2 Employee
3 Sales

I have got a "UserGroup" Table HOLDING ID'S as Foreign key.
The data in the TABLE is like this

-------
UserID GroupID
-------
1 1(Manager from "Group" Table
1 2(Employee)
1 3(Sales)
2 2(Employee)
2 3(Sales)
-------

Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names
for example "Manager|Employee|Sales|"

So if User 1 log in I need something like that
UserID (1)-->"Manager|Employee|Sales|"

Please help me how to write this query.

RegardsTo do this you can do it as -

declare @.roles varchar(100)

SET @.roles = ''

SELECT @.roles = @.roles + '|' + GroupName
FROM UserGroup JOIN GROUPS ON UserGroup.GroupID = Groups.GroupID
Where UserID = @.userID --PAssed userID to the stored procedure

SET @.roles = @.roles + '|'

SELECT @.roles|||...although this will give you a starting and ending pipe.

You might also try:

declare @.roles varchar(200)
SELECT @.roles = COALESCE('|'+G.Groupname, G.GroupName)
FROM UserGroup UG
INNER JOIN GROUPS G ON UG.GroupID = G.GroupID
WHERE UG.UserID = @.userID
SELECT @.Roles

...or simply create @.roles as an output parameter and you won't need to perform that final SELECT.|||Thanks a lot guys. I really appreciate this.
Is there any way of getting the UserID AS WELL

The select query should return

UserID, Groups(Pipe seperated)

The Example I mentioned above, according to that example, the return should be

UserID, Groups
---------
1, Manager|Employee|Sales|
----------------

Many, Many Thanks and Regards|||pjmcb -

How are you going to get one row with all the groupnames with your SQL? I tried it and it just gets the first record.

Am I missing something here?

Thanks,

AP|||No, it was I who was missing something.

Try:

declare @.roles varchar(200)
SELECT @.roles = COALESCE(@.roles+'|'+G.Groupname, G.GroupName)
FROM UserGroup UG
INNER JOIN GROUPS G ON UG.GroupID = G.GroupID
WHERE UG.UserID = @.userID
SELECT @.Roles

With the big difference being in the COALESCE parameter:
COALESCE(@.roles+'|'+G.Groupname, G.GroupName)
instead of
COALESCE('|'+G.Groupname, G.GroupName).

Sorry. My mistake, Was typing quickly. I still haven't tested it yet, but this should work for you...