如何为此要求编写存储过程 [英] How to write a stored procedure for this requirement
问题描述
大家好,
我有两张桌子表-1和表格-2我应该得到如下所示的结果表格
表-1
用户ID |姓名
1 | X
2 |是
表-2
UserID | RoleId
1 | 1
1 | 2
1 | 3
2 | 2
2 | 3
程序生成的表应该像
UserID |名称| RoleId
1 | X | 1,2,3
2 | Y | 2,3
这是我的迫切要求。请尽快发布您的答案。在此先感谢
问候,
Srivikas
这是你的商店程序:
创建 程序示例1
作为
开始
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(' < span class =code-string>'), TYPE )。value(' 。',' VARCHAR(MAX)')
, 1 , 1 ,SPACE( 0 )) FROM Tbl2 B,Tbl1 C
其中 B .userid = C.userid GROUP BY B.userid,C.name
结束
- 执行例1
输出:
userid名称Roleid
1 X 1,2,3
2 Y 2,3
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
It is an urgent requirement for me. Please post your answers as soon as possible. Thanks in advance
Regards,
Srivikas
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
这篇关于如何为此要求编写存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!