动态拼接sql

 

      ----日期行转列         --建表是为了列的顺序按一定的顺序排（聚集索引顺序）
 create table #mtd_column_list(resdate nvarchar(20))
create clustered index res_id on  #mtd_column_list(resdate)
 insert into #mtd_column_list(resdate)
 select * from (
 select top 100 percent resdate
from #mtd where 1=1
group by resdate
)a
        
declare @sqly nvarchar(max)
set @sqly ='select * from (select UPPER(region) [区域],city [城市], storecode [柜台编号],storename [柜台名称],''预约场次'' [数据汇总],MAX(case resdate when ''Total'' then cast(res_cnt as nvarchar(10)) else ''0'' end) as [Total]'

begin
  select  @sqly+=  ',MAX(case resdate when '''+ resdate +''' then cast(res_cnt as nvarchar(10)) else ''0'' end) as ['+case when resdate<>'Total' then ' '+ right(resdate,2)+N'月' else 'Total' end+']'  from #mtd_column_list a where resdate<>'Total'
end

select @sqly+=' from #mtd group by region,city,storecode,storename union all '
select @sqly+='  select  UPPER(region) region,city,storecode,storename,''排班场次'' kpiname,MAX(case resdate when ''Total'' then cast(open_cnt as nvarchar(10)) else ''0'' end) as [Total]'
select  @sqly+=  ',MAX(case resdate when '''+ resdate +''' then cast(open_cnt as nvarchar(10)) else ''0'' end) as ['+case when resdate<>'Total' then right(resdate,2) else 'Total' end+']'  from #mtd_column_list a where resdate<>'Total'
select @sqly+=' from #mtd group by region,city,storecode,storename union all '
select @sqly+=' select  UPPER(region) region,city,storecode,storename,''预约率'' kpiname,MAX(case resdate when ''Total'' then cast(cast(resrate as decimal(18,2)) as nvarchar(10))+''%'' else  ''0.00%'' end) as [Total]'
select  @sqly+=  ',MAX(case resdate when '''+ resdate +''' then cast(cast(resrate as decimal(18,2)) as nvarchar(10))+''%'' else ''0.00%'' end) as ['+case when resdate<>'Total' then right(resdate,2) else 'Total' end+']'  from #mtd_column_list a where resdate<>'Total'
select @sqly+=' from #mtd group by region,city,storecode,storename ) a order by [区域],[城市],[柜台名称],[数据汇总]'
--select @sqll
exec sp_executesql @sqly
--select * from #mtd