SQL SERVER2000將多行查詢結果拼接到一行數據及函數的創建

?

處理前的查詢結果如上圖：

?

通過借助SQL變量的定義

DECLARE @Scope varchar(1000)
DECLARE @Num int 
SET @Scope=''
SET   @Num = 1   
SELECT @Scope=@Scope+convert(varchar(8),@Num)+'、'+DescScope+ DescOper+';', @Num = @Num+1 From fuel_contractQualityNew Where ContractID='012009000100'
SELECT ContractID,@Scope FROM fuel_contractQualityNew Where ContractID='012009000100' GROUP BY ContractID

?

?

實現了一下效果：

?

 //創建拼接描述SQL的函數
CREATE FUNCTION dbo.f_str(@contractID varchar(50))
RETURNS VARCHAR(8000)
AS
BEGINDECLARE @Scope varchar(1000)DECLARE @Num int SET @Scope=''SET   @Num = 1   SELECT @Scope=@Scope+convert(varchar(8),@Num)+''+DescScope+ DescOper+';', @Num = @Num+1 From fuel_contractQualityNew where ContractID= @contractIDRETURN @Scope
END
GO

?