2

I have a report, which is developed in SSRS 2005 and i am using a stored procedure to fetch the results from the database. The result on the report output is very simple as shown below in the figure.

If suppose I am looking for different Members eg:-

 MemberID
c108
c109
c110
...

as shown in the figure below. So for each member there are various USERS and for each user there will be various ROLES. enter image description here

But I need all in one row as shown below.

MemberID  UserID    Roles
c108   22492   SystemOperator,ItemResearcher,Non-Decision Role,Security Administrator, Decisioning Administrator,Reporting,Corporate Administrator
c108   22469   SystemOperator,ItemResearcher,Non-Decision Role,Security Administrator,Decisioning Administrator

So that all roles for each user can be shown in one single row in SSRS (reporting) with ',' seperated.

What is the easiest way to write a stored procedure to produce a result just like this.

Please Note:- There may be some change, if my boss wants me to get the userId also in just 1 row with ',' seperated, then I must be able to modify the query easily. Please suggest me. Thank you so much for taking time to read and understand my question.

desi
  • 466
  • 1
  • 4
  • 22
  • 2
    Search for [SQL and Pivot](http://stackoverflow.com/search?q=SQL+pivot) on this site. – Oded Jul 25 '11 at 20:42
  • Check this question over here; http://stackoverflow.com/questions/1754674/how-do-i-merge-two-or-more-rows-based-on-their-foreign-key – Orson Jul 25 '11 at 21:13

2 Answers2

2

If this were MySQL, you'd use the GROUP_CONCAT function to create that comma separated list:

SELECT MemberID, UserID, GROUP_CONCAT(Role) AS Roles
FROM myTable
GROUP BY MemberID, UserID

However since this is MSSQL you'll need to fake GROUP_CONCAT something like this

Community
  • 1
  • 1
James
  • 20,957
  • 5
  • 26
  • 41
1

If you want to concatenate then you may want to look at Jeff Moden's article Performance Tuning: Concatenation Functions and Some Tuning Myths which goes through a variety of techniques.

I would generally use stuff and for XML path (discussed in the article) which looks roughly like:

SELECT t1.ID,        
   STUFF((SELECT ','+t2.datafield 
   FROM dbo.tablename2 t2 
                WHERE t1.ID = t2.ID FOR XML PATH('')),1,1,'')   
FROM dbo.TestData t1  
GROUP BY t1.ID
TimothyAWiseman
  • 14,385
  • 12
  • 40
  • 47