drop table #testcreate table #test (id varchar(20),[date] datetime,flag varchar(20)) insert into #test select 'A' , '2007-2-1' , 'TRUE' union all select 'A' , '2007-2-5' , 'TRUE' union all select 'A' , '2007-2-9' , 'TRUE' union allselect 'b' , '2007-2-1' , 'TRUE' union all select 'b' , '2007-2-5' , 'FLASE' union all select 'b' , '2007-2-9' , 'TRUE' select * from #testselect id, max(case when month([date])='01' then case when flag = 'TRUE' then 1 else 2 end else 0 end) as 一月, max(case when month([date])='02' then case when flag = 'TRUE' then 1 else 2 end else 0 end) as 二月, max(case when month([date])='03' then case when flag = 'TRUE' then 1 else 2 end else 0 end) as 三月 from #test group by id --(select distinct id from #test) a
id 一月 二月 三月 -------------------- ----------- ----------- ----------- A 0 1 0 b 0 2 0(所影响的行数为 2 行)
insert into #test
select 'A' , '2007-2-1' , 'TRUE' union all
select 'A' , '2007-2-5' , 'TRUE' union all
select 'A' , '2007-2-9' , 'TRUE' union allselect 'b' , '2007-2-1' , 'TRUE' union all
select 'b' , '2007-2-5' , 'FLASE' union all
select 'b' , '2007-2-9' , 'TRUE'
select * from #testselect id,
max(case when month([date])='01' then case when flag = 'TRUE' then 1 else 2 end else 0 end) as 一月,
max(case when month([date])='02' then case when flag = 'TRUE' then 1 else 2 end else 0 end) as 二月,
max(case when month([date])='03' then case when flag = 'TRUE' then 1 else 2 end else 0 end) as 三月
from #test group by id --(select distinct id from #test) a
-------------------- ----------- ----------- -----------
A 0 1 0
b 0 2 0(所影响的行数为 2 行)