13,052,351 members (55,434 online)
Rate this:
See more:
Hi everybody,
I have two tables Table -1 and Table -2 from which I should get resultant table as shown below

Table -1

UserID | Name

1 | X
2 | Y

Table -2

UserID | RoleId

1 | 1
1 | 2
1 | 3
2 | 2
2 | 3

The resultant table from the procedure should be like

UserID | Name | RoleId

1 | X | 1,2,3
2 | Y | 2,3

Regards,
Srivikas
Posted 22-Mar-13 21:17pm
phil.o 23-Mar-13 3:58am

Reported as not a question - This is not a code-ordering forum ; you have to make an effort about the problem which is presented to you
Homework has been assigned to you so you can learn what you have to. It's silly to ask someone else to do it for you without trying to think about the problem.
Moreover, reporting something as urgent is considered as rude on this forum. It won't make things go faster.
Srivikas 23-Mar-13 15:48pm

Its ok.. I solved it myself. I posted it when I was in a dilemma thinking of the solution.Thank u

Rate this:

## Solution 2

Here is your Store Procedure :

```create procedure Example1
As
begin

SELECT B.userid ,C.Name,
Roleid = STUFF ( ( SELECT ','+A.Roleid FROM Tbl2 A
WHERE A.userid = B.userid ORDER BY A.userid
FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)')
, 1,1,SPACE(0)) FROM Tbl2 B, Tbl1 C
where B.userid=C.userid GROUP BY B.userid ,C.name

End```

`--Exec Example1`

Output :

```userid	Name	Roleid
1	X	1,2,3
2	Y	2,3```
Srivikas 26-Mar-13 4:13am

Thank you

Top Experts
Last 24hrsThis month
 OriginalGriff 313 David_Wimbley 100 Jochen Arndt 95 Richard MacCutchan 95 ppolymorphe 90
 OriginalGriff 5,536 RickZeeland 1,944 ppolymorphe 1,848 F-ES Sitecore 1,636 Dave Kreskowiak 1,409