SQL中把符合条件的记录里的值输出到一行里

[谢迅]

CLEAR PROC [dbo].[SP_FHTZD_YSSL2] @DJLSH INT AS

--声明变量：@khhz默认为空值，@1默认为1，@2取FHTZDcsB表体djbth最大值(或者取表头里djbtzdh进行赋值)
declare @khhz varchar(255)='',@1 INT=1 ,@2 INT 

--SELECT @2=MAX(DJBTH) FROM FHTZDcsB WHERE DJLSH=@DJLSH
select @2=DjBtZdh from FHTZDCSH WHERE DJLSH=@DJLSH

WHILE (@1<=@2) 
	BEGIN 
		IF  EXISTS(SELECT 1 FROM FHTZDcsB  WHERE DJLSH=@DJLSH  AND DJBTH=@1)
		BEGIN

			SET @khhz=''
			Select @khhz=@khhz+IsNull(S.ZYSPB,'')+';'      
			From FHTZDcsS S, FHTZDcsB B     
			where S.djlsh=@DJLSH 
			AND S.DJLSH=B.DjLsh    
			AND S.DJBTH=B.DJBTH
			AND S.DJBTH=@1
			--Group By S.DjBth ,ZYSPB   
			SELECT @khhz=SUBSTRING(@khhz,1,LEN(@khhz)- 1)		--对@khhz进行处理（去除最后一个;字符）。
			UPDATE FHTZDcsB SET FYSSLMX=@khhz WHERE DJLSH=@DJLSH AND DJBTH=@1
		END
	SET @1=@1+1
	END 

 

 

declare @text varchar(255)='',
		 @id varchar(255)=''

--对于满足zzzt = '在职'且BMMC = 'N'条件的每一行记录，它将YGXM（如果不为空）的值连接到@text变量后面并加上;,将YGH（如果不为空）的值连接到@id变量后面并加上,
 select @text =@text+isnull(YGXM,'')+';',
		@id	= @id+isnull(YGH,'')+','
 from UserH where zzzt='在职' and BMMC='N'
 --Group By YGXM

 print @text
 print	@id