SQL subquery and stuff function (turns the multi-role and multi-department of the same person into a string)

Use [erp2015]go/****** object:storedprocedure [dbo].    [GetUser] Script date:03/14/2015 13:27:04 ******/set ansi_nulls ongoset quoted_identifier ongo--=============================== ==============--author:wangyanling--Create date:205-03-12--Description: Get customer information--================================ =============alter PROCEDURE [dbo].  [GetUser]--ADD the parameters for the stored procedure here@uname varchar ($) asbegin--SET NOCOUNT on added to prevent Extra result sets from--interfering with SELECT statements. SET NOCOUNT on; CREATE TABLE #temp (userid int, gname VARCHAR) CREATE TABLE #temp2 (userid int,gname VARCHAR) insert INTO #t  EMP2 SELECT u.uid,db.gname from U_user uleft join User_group ug in U.uid=ug.uidleft joindb_group db on Ug.gid=db.gidinsert Into #temp select U.uid,g.gname from U_user uleft joins Ug_user_group ug on U.uid=ug. Uidleft join G_group G on UG. Gid=g.gid declare @count int begin SELECT DISTINCT u_user. UId, Lname,worknum,uname,uqq,utYpe,totime, Remark=stuff (select ', ' +rtrim (#temp. Gname) from #temp where t.userid= #temp. USerID ORDER by #temp. USerID FOR XML Path (")), Probation=stuff ("), "(SELECT ', ' +rtrim (#temp2. Gname) from #temp2 where T2. Userid= #temp2. USerID ORDER by #temp2. USerID FOR XML Path ("))," U_user ") from the left join #temp T on U_user. Uid=t.userid left joins #temp2 T2 on U_user. Uid=t2. USerID where UName like '% ' [email protected]+ '% ' end drop table #temp drop table #te Mp2end--exec GetUser '

SQL subquery and stuff function (turns the multi-role and multi-department of the same person into a string)