sql 子查詢與stuff函數（把相同人的多角色與多部門變成字串），sqlstuff

sql 子查詢與stuff函數（把相同人的多角色與多部門變成字串），sqlstuff

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 uleft join User_Group ug on u.UId=ug.uIdleft joinDb_Group db on ug.gId=db.gIdinsert into #temp  select u.UId,g.GName from u_user uleft join 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('')),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 #temp2END--exec GetUser ''