SQL:行合并问题 & 使用 SP_executesql 从exec('SQL语句') 中得到一个返回值

行合并问题：SQL语句中一条常见的行合并问题

 使用 SP_executesql 从exec('SQL语句') 中得到一个返回值

另：行转列,列分行,行合并列,etc. （整理贴）

 

 自己写的代码：自己加工到可以将连续的号码用“-”表示，如：10-19

  1 -- =============================================
  2 -- Create AgrNumber Number Stat procedure
  3 -- 查询某所属期的协议书使用情况（如查询所属期为200903的数据，请设置参数 @sdate = '200903'）
  4 -- 分公司名称不能空
  5 -- =============================================
  6 
  7 -- Drop stored procedure if it already exists
  8 IF EXISTS (
  9   SELECT * 
 10     FROM INFORMATION_SCHEMA.ROUTINES 
 11    WHERE SPECIFIC_SCHEMA = N'dbo'
 12      AND SPECIFIC_NAME = N'AgreementNumberStat' 
 13 )
 14    DROP PROCEDURE dbo.AgreementNumberStat
 15 GO
 16 
 17 CREATE PROCEDURE dbo.AgreementNumberStat
 18     @sdate varchar(6) = convert(varchar(6),GetDate(),112)--查询所属期
 19     @company varchar(8) = '%%'--查询分公司
 20 
 21 AS    
 22     declare @misdata varchar(10)--分公司对应的数据库名称
 23     declare @strsql nvarchar(2000)
 24     
 25     select @misdata = DataBaseName from FilialeInfor where ForShort like @company--获取分公司对应的数据库
 26     
 27 DECLARE @Result TABLE
 28 (
 29     ID int IDENTITY(1,1),
 30     NumberPrefix smallint,
 31     UsedAgrNumbers varchar(max),--已经使用的协议编号
 32     UsedAmount int,--已使用数
 33     UnUsedAgrNumbers varchar(max),--未使用的协议编号
 34     UnUsedAmount int,--未使用数
 35     DisuseAgrNumbers varchar(max),--作废的协议编号
 36     DisuseAmount int--作废数
 37 )
 38 
 39 if @misdata is null 
 40     SELECT NumberPrefix,UsedAgrNumbers,UsedAmount,UnUsedAgrNumbers,UnUsedAmount,DisuseAgrNumbers,DisuseAmount 
 41     FROM @Result
 42 else
 43 begin
 44     set @strsql = ' select NumberPrefix'
 45             + ' ,sum(case when convert(varchar(6),UsedDate,112) = ' + @sdate + ' then 1 else 0 end) as Used'
 46             + ' ,sum(case when (UsedDate is null or convert(varchar(8),UsedDate,112) > ' + @sdate + '31) then 1 else 0 end) as UnUsed'
 47             + ' ,sum(case when convert(varchar(6),ChangeDate,112) = ' + @sdate 
 48             + ' and (agr.Status = 4) then 1 else 0 end) as Disuse'
 49             + ' from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
 50             + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
 51             + ' where OutputCompany like ''' + @company + ''''
 52             + ' and OutPutCompany is not NULL'
 53             + ' group by NumberPrefix'
 54     INSERT INTO @Result (NumberPrefix,UsedAmount,UnUsedAmount,DisuseAmount) 
 55     exec (@strsql)
 56 
 57     DECLARE @i int
 58             ,@j int
 59             ,@numberprefix smallint
 60             ,@agrnums nvarchar(max)
 61 
 62     SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
 63     WHILE @i<=@j
 64     BEGIN
 65         select @numberprefix = NumberPrefix from @Result Where ID = @i
 66     
 67         --获取已经使用的编号
 68         set @strsql = 'Declare @tmpnum1 bigint,@tmpnum2 bigint'
 69                     + ' SET @agrnumbers = '''' SET @tmpnum1 = -2 set @tmpnum2 = -2'
 70                     + ' select @agrnumbers = @agrnumbers + case '
 71                     + ' when @tmpnum2 + 1 = AgrNumber then '''' '
 72                     + ' when @agrnumbers = '''' then convert(varchar(20),AgrNumber) '
 73                     + ' when @tmpnum1 + 1 <> @tmpnum2 then '','' + convert(varchar(20),AgrNumber) '
 74                     + ' else ''-'' + convert(varchar(20),@tmpnum2) + '','' + convert(varchar(20),AgrNumber)  '
 75                     + ' end, @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber'
 76                     + ' from (select an.AgrNumber from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
 77                     + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
 78                     + ' where OutputCompany like ''' + @company + ''''
 79                     + ' and OutPutCompany is not NULL'
 80                     + ' and convert(varchar(6),UsedDate,112) = ' + @sdate
 81                     + ' and NumberPrefix = ' + convert(varchar(8),@numberprefix)
 82                     + ' union select 9223372036854775807) as tmpt'
 83         execute SP_executesql @strsql,N'@agrnumbers varchar(max) output',@agrnums output
 84         IF(len(@agrnums) > 20)
 85             UPDATE @Result SET UsedAgrNumbers = left(@agrnums , len(@agrnums) - 20) WHERE ID = @i
 86     
 87         --获取未使用的编号
 88         set @strsql = 'Declare @tmpnum1 bigint,@tmpnum2 bigint'
 89                     + ' SET @agrnumbers = '''' SET @tmpnum1 = -2 set @tmpnum2 = -2'
 90                     + ' select @agrnumbers = @agrnumbers + case '
 91                     + ' when @tmpnum2 + 1 = AgrNumber then '''' '
 92                     + ' when @agrnumbers = '''' then convert(varchar(20),AgrNumber) '
 93                     + ' when @tmpnum1 + 1 <> @tmpnum2 then '','' + convert(varchar(20),AgrNumber) '
 94                     + ' else ''-'' + convert(varchar(20),@tmpnum2) + '','' + convert(varchar(20),AgrNumber)  '
 95                     + ' end, @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber'
 96                     + ' from (select an.AgrNumber from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
 97                     + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
 98                     + ' where OutputCompany like ''' + @company + ''''
 99                     + ' and OutPutCompany is not NULL'
                     + ' and (UsedDate is null or convert(varchar(8),UsedDate,112) > ' + @sdate + '31)'
                     + ' and NumberPrefix = ' + convert(varchar(8),@numberprefix)
                     + ' union select 9223372036854775807) as tmpt'
         execute SP_executesql @strsql,N'@agrnumbers varchar(max) output',@agrnums output
         IF(len(@agrnums) > 20)
             UPDATE @Result SET UnUsedAgrNumbers = left(@agrnums , len(@agrnums) - 20) WHERE ID = @i
     
         --获取作废的编号
         set @strsql = 'Declare @tmpnum1 bigint,@tmpnum2 bigint'
                     + ' SET @agrnumbers = '''' SET @tmpnum1 = -2 set @tmpnum2 = -2'
                     + ' select @agrnumbers = @agrnumbers + case '
                     + ' when @tmpnum2 + 1 = AgrNumber then '''' '
                     + ' when @agrnumbers = '''' then convert(varchar(20),AgrNumber) '
                     + ' when @tmpnum1 + 1 <> @tmpnum2 then '','' + convert(varchar(20),AgrNumber) '
                     + ' else ''-'' + convert(varchar(20),@tmpnum2) + '','' + convert(varchar(20),AgrNumber)  '
                     + ' end, @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber'
                     + ' from (select an.AgrNumber from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
                     + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
                     + ' where OutputCompany like ''' + @company + ''''
                     + ' and OutPutCompany is not NULL'
                     + ' and convert(varchar(6),ChangeDate,112) = ' + @sdate
                     + ' and (agr.Status = 4)'
                     + ' and NumberPrefix = ' + convert(varchar(8),@numberprefix)
                     + ' union select 9223372036854775807) as tmpt'
         execute SP_executesql @strsql,N'@agrnumbers varchar(max) output',@agrnums output
         IF(len(@agrnums) > 20)
             UPDATE @Result SET DisuseAgrNumbers = left(@agrnums , len(@agrnums) - 20) WHERE ID = @i
     
         SET @i=@i+1
     END
     SELECT NumberPrefix,UsedAgrNumbers,UsedAmount,UnUsedAgrNumbers,UnUsedAmount,DisuseAgrNumbers,DisuseAmount 
     FROM @Result
 end--end if
 
 GO
 

 

  1 -- =============================================
  2 -- Create AgrNumber UnOutPut Amount procedure
  3 -- 查询某日期的当前库存总数
  4 -- =============================================
  5 
  6 -- Drop stored procedure if it already exists
  7 IF EXISTS (
  8   SELECT * 
  9     FROM INFORMATION_SCHEMA.ROUTINES 
 10    WHERE SPECIFIC_SCHEMA = N'dbo'
 11      AND SPECIFIC_NAME = N'AgrUnOutputStat' 
 12 )
 13    DROP PROCEDURE dbo.AgrUnOutputStat
 14 GO
 15 
 16 CREATE PROCEDURE dbo.AgrUnOutputStat
 17     @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
 18 
 19 AS    
 20     DECLARE @Result TABLE
 21     (
 22         ID int IDENTITY(1,1),
 23         NumberPrefix smallint,--前缀
 24         AgrNumbers varchar(max),--库存编号（连续编号用“-”间隔，非连续用“,”间隔）
 25         Amount int--统计数
 26     )
 27     INSERT INTO @Result (NumberPrefix,Amount) 
 28     select NumberPrefix
 29     ,Count(AgrNumber) as UnOutput--[未出库数/未分配给分公司(库存)]
 30     from AgreementNumber
 31     where OutputDate is null or convert(varchar(8),OutputDate,112) >= @sdate
 32     group by NumberPrefix
 33     
 34     DECLARE @i int
 35             ,@j int
 36             ,@tmpnum1 bigint--前2个的AgrNumber
 37             ,@tmpnum2 bigint--前1个的AgrNumber
 38             ,@agrnumbers nvarchar(max)
 39     
 40     SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
 41     WHILE @i<=@j
 42     BEGIN
 43         SET @agrnumbers = ''
 44         SET @tmpnum1 = -2
 45         set @tmpnum2 = -2
 46     
 47         --获取库存编号
 48         select 
 49         @agrnumbers = @agrnumbers + 
 50         case 
 51         when @tmpnum2 + 1 = AgrNumber then '' 
 52         when @agrnumbers = '' then convert(varchar(20),AgrNumber) 
 53         when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
 54         else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)  
 55         end
 56         , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
 57         from 
 58         (
 59             select AgrNumber
 60             from AgreementNumber
 61             where (OutputDate is null or convert(varchar(8),OutputDate,112) >= @sdate)
 62             and NumberPrefix = (select NumberPrefix from @Result Where ID = @i)
 63             union
 64             select 9223372036854775807--max bigint
 65         ) as tmpt
 66     
 67         --更新编号
 68         IF(len(@agrnumbers) > 20)
 69             UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
 70         SET @i=@i+1
 71     END
 72     SELECT NumberPrefix,AgrNumbers,Amount FROM @Result
 73 
 74 GO
 75 
 76 
 77 -- =============================================
 78 -- Create AgrNumber Output and UnClaimed Amount procedure
 79 -- 查询某日期分公司出库但未领用数/分公司未有人领用(分公司库存)
 80 -- 若参数 @company 为空结果为总数
 81 -- =============================================
 82 
 83 -- Drop stored procedure if it already exists
 84 IF EXISTS (
 85   SELECT * 
 86     FROM INFORMATION_SCHEMA.ROUTINES 
 87    WHERE SPECIFIC_SCHEMA = N'dbo'
 88      AND SPECIFIC_NAME = N'AgrOutputUnClaimed' 
 89 )
 90    DROP PROCEDURE dbo.AgrOutputUnClaimed
 91 GO
 92 
 93 CREATE PROCEDURE dbo.AgrOutputUnClaimed
 94     @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
 95     @company varchar(8) = '%%'--查询分公司
 96 
 97 AS    
 98     DECLARE @Result TABLE
 99     (
         ID int IDENTITY(1,1),
         NumberPrefix smallint,
         AgrNumbers varchar(max),
         Amount int
     )
     INSERT INTO @Result (NumberPrefix,Amount) 
     select NumberPrefix
     ,Count(AgrNumber) as OutputUnClaimed--[出库未领用数（分公司未有人领用）]
     from AgreementNumber
     where OutputCompany like @company
     and (convert(varchar(8),OutputDate,112) < @sdate)
     and (ClaimingDate is null or convert(varchar(8),ClaimingDate,112) >= @sdate)
     group by NumberPrefix
     
     DECLARE @i int
             ,@j int
             ,@tmpnum1 bigint
             ,@tmpnum2 bigint
             ,@agrnumbers nvarchar(max)
     
     SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
     WHILE @i<=@j
     BEGIN
         SET @agrnumbers = ''
         SET @tmpnum1 = -2
         set @tmpnum2 = -2
     
         --获取库存编号
         select 
         @agrnumbers = @agrnumbers + 
         case 
         when @tmpnum2 + 1 = AgrNumber then '' 
         when @agrnumbers = '' then convert(varchar(20),AgrNumber) 
         when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
         else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)  
         end
         , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
         from 
         (
             select AgrNumber
             from AgreementNumber
             where (convert(varchar(8),OutputDate,112) < @sdate) 
             and (ClaimingDate is null or convert(varchar(8),ClaimingDate,112) >= @sdate)
             and OutputCompany like @company
             and NumberPrefix = (select NumberPrefix from @Result Where ID = @i)
             union
             select 9223372036854775807--max bigint
         ) as tmpt
     
         --更新编号
         IF(len(@agrnumbers) > 20)
             UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
         SET @i=@i+1
     END
     SELECT NumberPrefix,AgrNumbers,Amount FROM @Result
 
 GO
 
 
 -- =============================================
 -- Create AgrNumber Claimed and UnUsed Amount procedure
 -- 查询某日期分公司人员领用但未使用数
 -- 若参数 @company 为空结果为总数
 -- =============================================
 
 -- Drop stored procedure if it already exists
 IF EXISTS (
   SELECT * 
     FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE SPECIFIC_SCHEMA = N'dbo'
      AND SPECIFIC_NAME = N'AgrClaimedUnUsed' 
 )
    DROP PROCEDURE dbo.AgrClaimedUnUsed
 GO
 
 CREATE PROCEDURE dbo.AgrClaimedUnUsed
     @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
     @company varchar(8) = '%%'--查询分公司
 
 AS    
     DECLARE @Result TABLE
     (
         ID int IDENTITY(1,1),
         NumberPrefix smallint,
         AgrNumbers varchar(max),
         ClaimedUnUsedAmount int
     )
     INSERT INTO @Result (NumberPrefix,ClaimedUnUsedAmount) 
     select NumberPrefix
     ,Count(AgrNumber) as ClaimedUnUsed--[领用未使用数]
     from AgreementNumber
     where convert(varchar(8),OutputDate,112) < @sdate--出库日期小于查询日期（在查询日期前已经被分公司领取）
     and (convert(varchar(8),ClaimingDate,112) < @sdate)
     and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
     and OutputCompany like @company
     group by NumberPrefix
     
     DECLARE @i int
             ,@j int
             ,@tmpnum1 bigint
             ,@tmpnum2 bigint
             ,@agrnumbers nvarchar(max)
     
     SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
     WHILE @i<=@j
     BEGIN
         SET @agrnumbers = ''
         SET @tmpnum1 = -2
         set @tmpnum2 = -2
     
         --获取库存编号
         select 
         @agrnumbers = @agrnumbers + 
         case 
         when @tmpnum2 + 1 = AgrNumber then '' 
         when @agrnumbers = '' then convert(varchar(20),AgrNumber) 
         when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
         else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)  
         end
         , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
         from 
         (
             select AgrNumber
             from AgreementNumber
             where convert(varchar(8),OutputDate,112) < @sdate--出库日期小于查询日期（在查询日期前已经被分公司领取）
             and (convert(varchar(8),ClaimingDate,112) < @sdate) 
             and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
             and OutputCompany like @company
             and NumberPrefix = (select NumberPrefix from @Result Where ID = @i)
             union
             select 9223372036854775807--max bigint
         ) as tmpt
     
         --更新编号
         IF(len(@agrnumbers) > 20)
             UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
         SET @i=@i+1
     END
     SELECT NumberPrefix,AgrNumbers,ClaimedUnUsedAmount FROM @Result
 
 GO
 
 
 -- =============================================
 -- Create AgrNumber Claimed and UnUsed Amount With Claiming Date procedure
 -- 查询某日期分公司人员领用但未使用情况(按协议书类别，领用日期分类汇总)
 -- 若参数 @company 为空结果为总数
 -- =============================================
 
 -- Drop stored procedure if it already exists
 IF EXISTS (
   SELECT * 
     FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE SPECIFIC_SCHEMA = N'dbo'
      AND SPECIFIC_NAME = N'AgrClaimedUnUsedWithDate' 
 )
    DROP PROCEDURE dbo.AgrClaimedUnUsedWithDate
 GO
 
 CREATE PROCEDURE dbo.AgrClaimedUnUsedWithDate
     @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
     @company varchar(8) = '%%'--查询分公司
 
 AS    
     DECLARE @Result TABLE
     (
         ID int IDENTITY(1,1),
         NumberPrefix smallint,
         AgrNumbers varchar(max),
         ClaimingDate smalldatetime,
         ClaimedUnUsedAmount int
     )
     INSERT INTO @Result (NumberPrefix,ClaimingDate,ClaimedUnUsedAmount) 
     select NumberPrefix,convert(varchar(10),ClaimingDate,102) as CDate
     ,Count(AgrNumber) as ClaimedUnUsed--[领用未使用数]
     from AgreementNumber
     where OutputCompany like @company
     and convert(varchar(8),OutputDate,112) < @sdate--出库日期小于查询日期（在查询日期前已经被分公司领取）
     and (convert(varchar(8),ClaimingDate,112) < @sdate)
     and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
     group by NumberPrefix,convert(varchar(10),ClaimingDate,102)
     
     DECLARE @i int
             ,@j int
             ,@tmpnum1 bigint
             ,@tmpnum2 bigint
             ,@agrnumbers nvarchar(max)
             ,@numberprefix smallint
             ,@claimingdate varchar(10)
     
     SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
     WHILE @i<=@j
     BEGIN
         SET @agrnumbers = ''
         SET @tmpnum1 = -2
         set @tmpnum2 = -2
     
         select @numberprefix = NumberPrefix,@claimingdate = convert(varchar(10),ClaimingDate,102) from @Result Where ID = @i
     
         --获取库存编号
         select 
         @agrnumbers = @agrnumbers + 
         case 
         when @tmpnum2 + 1 = AgrNumber then '' 
         when @agrnumbers = '' then convert(varchar(20),AgrNumber) 
         when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
         else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)  
         end
         , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
         from 
         (
             select AgrNumber
             from AgreementNumber
             where convert(varchar(8),OutputDate,112) < @sdate
             and (convert(varchar(8),ClaimingDate,112) < @sdate) 
             and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
             and OutputCompany like @company
             and NumberPrefix = @numberprefix
             and convert(varchar(10),ClaimingDate,102) = @claimingDate
             union
             select 9223372036854775807--max bigint
         ) as tmpt
     
         --更新编号
         IF(len(@agrnumbers) > 20)
             UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
         SET @i=@i+1
     END
     SELECT NumberPrefix,AgrNumbers,ClaimingDate,ClaimedUnUsedAmount FROM @Result
 
 GO