首页 > 代码库 > SQL SERVER2000将多行查询结果拼接到一行数据

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=012009000100SELECT ContractID,@Scope FROM fuel_contractQualityNew Where ContractID=012009000100 GROUP BY ContractID

 

 

实现了一下效果:

技术分享

 

  //创建拼接描述SQL的函数CREATE FUNCTION dbo.f_str(@contractID varchar(50))RETURNS VARCHAR(8000)ASBEGIN        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= @contractID        RETURN @ScopeENDGO

 

SQL SERVER2000将多行查询结果拼接到一行数据