SQLServer 使用变量动态行转列

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

drop table #test
create table #test
(
    id int identity(1,1) primary key,
    bizDate varchar(50),
    type varchar(50),
    qty float
)

insert into #test
select '20110501','A',20.5 union all
select '20110501','B',98 union all
select '20110501','C',100.5 union all
select '20110501','A',32 union all
select '20110501','C',76.8 union all
select '20110502','B',58 union all
select '20110502','A',111 union all
select '20110502','A',51 union all
select '20110502','A',85 union all
select '20110502','B',52 union all
select '20110502','C',43 union all
select '20110503','A',158 union all
select '20110503','C',58 union all
select '20110503','B',28 union all
select '20110503','B',65 union all
select '20110503','A',11 union all
select '20110503','A',25 union all
select '20110503','C',63

declare @sql varchar(8000)
set @sql = 'select type'
select @sql = @sql + ' , SUM(CASE WHEN bizDate=''' + bizDate + ''' then qty else 0 end) [' + bizDate + ']'
from (select distinct bizDate from #test) as a order by bizDate--此行的SQL用于找出不重复的日期，也就是结果集中所有的日期
set @sql = @sql + ' from #test A group by type'
print @sql
exec(@sql)

--打印出来的完整SQL是：
select type ,
       SUM(CASE WHEN bizDate='20110501' then qty else 0 end) [20110501] ,
       SUM(CASE WHEN bizDate='20110502' then qty else 0 end) [20110502] ,
       SUM(CASE WHEN bizDate='20110503' then qty else 0 end) [20110503]
       from #test A group by type