SQL subquery and stuff functions (convert multiple roles and multiple departments into strings), sqlstuff

SQL subquery and stuff functions (convert multiple roles and multiple departments into strings), 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: obtain customer information -- ================================================== ========= 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''