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 ''