一个分类汇总的查询问题

[hpamweeq53]

表为djb
a_Name a_date1 a_dj a_xm

aa 2006-6-20 1 6
aa 2006-6-20 1 7
aa 2006-6-20 1 8
aa 2006-6-20 2 9

bb 2006-6-20 1 6
bb 2006-6-20 2 7
bb 2006-6-20 1 8
bb 2006-6-20 1 9

CC 2006-6-20 1 6
CC 2006-6-20 2 7
CC 2006-6-20 2 8
CC 2006-6-20 1 9


aa 2006-6-21 2 6
aa 2006-6-21 1 7
aa 2006-6-21 2 8
aa 2006-6-21 2 9

bb 2006-6-21 1 6
bb 2006-6-21 2 7
bb 2006-6-21 1 8
bb 2006-6-21 2 9

CC 2006-6-21 1 6
CC 2006-6-21 2 7
CC 2006-6-21 2 8
CC 2006-6-21 2 9

想要的结果为
a_xm 2006_6_20日a_dj为1的人数 2006_6_20日a_dj为2的人数 2006_6_21日a_dj为2的人数 2006_6_21日a_dj为1的人数

[smalltalks]

create table #t(a_Name varchar(10), a_date1 varchar(10), a_dj int, a_xm int)
insert into #t
select 'aa', '2006-6-20', 1, 6
union all select 'aa', '2006-6-20', 1, 7
union all select 'aa', '2006-6-20', 1, 8
union all select 'aa', '2006-6-20', 2, 9
union all select 'bb', '2006-6-20', 1, 6
union all select 'bb', '2006-6-20', 2, 7
union all select 'bb', '2006-6-20', 1, 8
union all select 'bb', '2006-6-20', 1, 9
union all select 'CC', '2006-6-20', 1, 6
union all select 'CC', '2006-6-20', 2, 7
union all select 'CC', '2006-6-20', 2, 8
union all select 'CC', '2006-6-20', 1, 9
union all select 'aa', '2006-6-21', 2, 6
union all select 'aa', '2006-6-21', 1, 7
union all select 'aa', '2006-6-21', 2, 8
union all select 'aa', '2006-6-21', 2, 9
union all select 'bb', '2006-6-21', 1, 6
union all select 'bb', '2006-6-21', 2, 7
union all select 'bb', '2006-6-21', 1, 8
union all select 'bb', '2006-6-21', 2, 9
union all select 'CC', '2006-6-21', 1, 6
union all select 'CC', '2006-6-21', 2, 7
union all select 'CC', '2006-6-21', 2, 8
union all select 'CC', '2006-6-21', 2, 9
declare @sql varchar(8000)
select @sql=isnull(@sql,'')+',['+a_date1+'为'+cast(a_dj as varchar)+'的人数]=sum(case when a_date1='''+a_date1+''' and a_dj='''+cast(a_dj as varchar)+''' then 1 else 0 end)'
from (select a_date1,a_dj from #t group by a_date1,a_dj) a
exec('select a_xm'+@sql+' from #t group by a_xm')
drop table #t

[liangpei2008]

--这样？
Select Max(a_xm),Count(a_name) as a_name,a_dj
From djb
Group by A_date1,a_dj