通过Case When语句作行列转换
在DT
现在写出解决方案:
问题描述:
有一张表,数据如下:
/* Date Tag 2010-01-01 是 2010-01-01 是 2010-01-01 是 2010-01-02 否 2010-01-02 否 */
要求转换结果:
/* 转换结果: Date 是 否 2010-01-01 3 0 2010-01-02 0 2 */
用Case When语句解决:
select TheDate,SUM(case Tag when '是' then 1 else 0 end) [是], SUM(case Tag when '否' then 1 else 0 end) [否] from #Test group by TheDate
以上SQL语句,可以通过构造变量了实现:
create table #Test ( TheDate varchar(30), Tag varchar(10) ) go insert into #Test values ('2010-01-01','是'); insert into #Test values ('2010-01-01','是'); insert into #Test values ('2010-01-01','是'); insert into #Test values ('2010-01-02','否'); insert into #Test values ('2010-01-02','否'); select * from #Test; --drop table #Test; declare @tempSelect varchar(4000); set @tempSelect = 'select TheDate'; select @tempSelect += ',SUM(case Tag when '''+ Tag +''' then 1 else 0 end) ['+ Tag +']' from (select distinct Tag from #Test) as tab;--重点在这句话select distinct Tag from #Test select @tempSelect += ' ' + 'from #Test group by TheDate;' print @tempSelect exec(@tempSelect)
执行结果:
KO了,理解了。但我对于‘养起’还是不理解,算了,理解不了。