行列转换

CREATE proc [dbo].[R_Parities_Config_CurrencyExchangeRate_V2]
    @FromCode nvarchar(400),
    @ToCode nvarchar(400),
    @EffectiveDate nvarchar(400),
    @ChangeDate nvarchar(400)
    as
    begin
        SET NOCOUNT ON;
        select f.col as FromCode into #FromTable from (select distinct col from dbo.[Split](@FromCode, ',')) as f
            where f.col in (select distinct FromCode from Config_CurrencyExchangeRate where ToCode = @ToCode and FromCode != @ToCode)
        select * into #ToTable from Config_CurrencyExchangeRate
            where ToCode = @ToCode and EffectiveDate >=@EffectiveDate and EffectiveDate <=@ChangeDate
        
        select a.EffectiveDate,c.FromCode+'/'+a.ToCode as CurrencyType,a.Rate into #a from #ToTable as a
        right join #FromTable as c on c.FromCode = a.FromCode order by a.EffectiveDate desc
 
    declare @groupField varchar(1000)
  select @groupField=isnull(@groupField,'')+case when isnull(@groupField,'')='' then '' else ',' end+QUOTENAME(CurrencyType)
  from (select rtrim(CurrencyType) as CurrencyType from (select distinct CurrencyType from #a) as a )t  
  declare @sql nvarchar(4000)
  set @sql=N'
  select *
  from
        (select  EffectiveDate,rtrim(CurrencyType) as CurrencyType,
   MAX(Rate) as Rate  
        from #a
        group by EffectiveDate,rtrim(CurrencyType)
        ) as x
   pivot (MAX(Rate)for CurrencyType in ('+@groupField+')) as pvt
   order by EffectiveDate desc'
  EXEC (@sql)
 
  drop table #a

    end
 
 --execute [R_Parities_Config_CurrencyExchangeRate_V2] 'CAD,USD','USD','2010-12-12','2016-12-12'
 
 --select distinct ToCode,FromCode from Config_CurrencyExchangeRate
GO