select convert(varchar(12),time,112),
[maths]=count(case when flag=maths then 1 else 0 end),
[music]=count(case when flag=music then 1 else 0 end),
[english]= count(case when flag=english then 1 else 0 end),
form a
group by convert(varchar(12),time,112)
[maths]=count(case when flag=maths then 1 else 0 end),
[music]=count(case when flag=music then 1 else 0 end),
[english]= count(case when flag=english then 1 else 0 end),
form a
group by convert(varchar(12),time,112)
FROM A GROUP BY CONVERT(CHAR(10), LesTime, 120),Flag
select convert(varchar(12),time,112),
[maths]=sum(case when flag='maths' then 1 else 0 end),
[music]=sum(case when flag='music' then 1 else 0 end),
[english]= sum(case when flag='english' then 1 else 0 end)
from table2
group by convert(varchar(12),time,112)
insert into t1 select '1','2004/11/11','maths'
union all
select '2','2004/11/12','music'
union all
select '3','2004/11/12','maths'
union all
select '4','2004/11/12','maths'
union all
select '5','2004/11/13','music'
union all
select '6','2004/11/11','english'select convert(varchar(12),time,112),
[maths]=sum(case when flag='maths' then 1 else 0 end),
[music]=sum(case when flag='music' then 1 else 0 end),
[english]= sum(case when flag='english' then 1 else 0 end)
from t1
group by convert(varchar(12),time,112)