sql 子查询与stuff函数（把相同人的多角色与多部门变成字符串）

USE [erp2015]
GO
/****** Object:  StoredProcedure [dbo].[GetUser]    Script Date: 03/14/2015 13:27:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		wangyanling
-- Create date: 205-03-12
-- Description:	获取客户信息
-- =============================================
ALTER PROCEDURE [dbo].[GetUser] 
	-- Add the parameters for the stored procedure here
	@UName varchar(200)
AS
BEGIN 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
	Create table #temp
	(
	    USerID int,
	   GName  VARCHAR(100)
	)
	create table #temp2
	(
	USerID int,
	GName  VARCHAR(100)
	)
	insert into #temp2 SELECT u.UId,db.gName FROM u_user u
	left join User_Group ug on u.UId=ug.uId
	left join
	Db_Group db on ug.gId=db.gId

	insert into #temp  select u.UId,g.GName from u_user u
		left join ug_User_Group ug on u.UId=ug.UId
		left 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('')),1,1,'') ,
      probation=STUFF((select ','+rtrim(#temp2.GName) from #temp2 where t2.USerID=#temp2.USerID 
      order by #temp2.USerID for xml path('')),1,1,'')
      from u_user 
      left join
       #temp t on u_user.UId=t.USerID
       left join 
       #temp2 t2 on u_user.UId=t2.USerID
     
       
       where UName like '%'+@UName+'%'
      
    end
    drop table #temp
    drop table #temp2
END
--exec GetUser ''