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

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:	获取客户信息-- =============================================ALTER PROCEDURE [dbo].[GetUser] 	-- Add the parameters for the stored procedure here	@UName varchar(200)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(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 '%'[email protected]+'%'          end    drop table #temp    drop table #temp2END--exec GetUser ''