Concatenating data from rows with same GroupBy col

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concatenating data from rows with same GroupBy col

Author

Topic

acpt
Starting Member

4 Posts

Posted - 2008-10-30 : 06:13:31

Hi,

I need to concatenate using the same 'group by' clause.
the grouping criteria is the same racf id or the user id.
The records to group come from a query and then i need to group them.

The query to get the results i need to group by is :

SELECT TblUsers.USER_ID,RACF_ID, ACCESS_LEVEL
FROM TblUsers INNER JOIN TblUserAccessLevel ON
TblUsers.User_Id = TblUserAccessLevel.User_Id
WHERE TblUserAccessLevel.Access_Level IN ('ACTUARIAL','OLYMPUS') AND TblUserAccessLevel.Sys_Id = 5;

The output of this query is:

--------------------------------------------------
User_Id RACF_Id Access_Level
--------------------------------------------------
45 BABBSM Actuarial
45 BABBSM Olympus
54 BARBERB Actuarial
54 BARBERB Olympus
110 BROWC33 Actuarial
110 BROWC33 Olympus
122 BURTONN Actuarial
122 BURTONN Olympus
192 FOSTEJ9 Actuarial
192 FOSTEJ9 Olympus
209 GARNHH3 Actuarial
209 GARNHH3 Olympus
--------------------------------------------------

Now , i need to concatanate in such a way that all the records with the either the same RACF_ID or User_Id gets concatenated together as under ..

--------------------------------------------------
User_Id RACF_Id Access_Level
--------------------------------------------------
45 BABBSM Actuarial,Olympus
54 BARBERB Actuarial,Olympus
110 BROWC33 Actuarial,Olympus
122 BURTONN Actuarial,Olympus
192 FOSTEJ9 Actuarial,Olympus
209 GARNHH3 Actuarial,Olympus
--------------------------------------------------

I dont want to use a UDF because i will be requiring 13 such different queries and making 13 different queries can be senseless.
However, if theres a way I can create a generic UDF that can be used for all the 13 queries, that should be okay.

I have created a query to get the concatenated records:

WITH USER_AL AS
(
SELECT TblUsers.USER_ID,RACF_ID, ACCESS_LEVEL
FROM TblUsers INNER JOIN TblUserAccessLevel ON
TblUsers.User_Id = TblUserAccessLevel.User_Id
WHERE TblUserAccessLevel.Access_Level IN
('ACTUARIAL','OLYMPUS') AND
TblUserAccessLevel.Sys_Id = 5
)
SELECT * from USER_AL;

Can someone please help me in getting ahead of this now ??
FYI, I am using SQL Server 2005.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 06:18:21

This is a task best handled in your front end application.

However. there are many, many. posts on exactly this problem.

Have a search for "rows to columns" and you'll find a hundred examples.

Regards,

-------------
Charlie