select ywsj,sum(case XinXiLY when '学生' then 1 else 0 end) [学生],
            sum(...) [],
            sum(...) []
from tb
group by ywsj

------解决方案--------------------
SQL code

create TABLE [test]
([ywSJ] [datetime] NOT NULL ,
[XinXiLY] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )

insert into test select '2012-01-01',N'学生'
insert into test select '2012-01-01',N'老师'  
insert into test select '2012-01-01',N'老师'  
insert into test select '2012-01-01',N'学生'  
insert into test select '2012-01-01',N'工人'  
insert into test select '2012-01-01',N'工人'  
insert into test select '2012-01-01',N'学生'  
insert into test select '2012-01-01',N'工人'  
insert into test select '2012-01-01',N'学生'

insert into test select '2012-01-02',N'学生'
insert into test select '2012-01-02',N'老师'  
insert into test select '2012-01-02',N'老师'  
insert into test select '2012-01-02',N'学生'  
insert into test select '2012-01-02',N'工人'  
insert into test select '2012-01-02',N'工人'  
insert into test select '2012-01-03',N'学生'  
insert into test select '2012-01-03',N'工人'  
insert into test select '2012-01-03',N'学生'  
 


select * from
(select * from test) p
pivot
(
 count(Xinxily)
 for XinxiLy in ([学生],[工人],[老师])
)as pvt

/*
ywSJ    学生    工人    老师
2012-01-01 00:00:00.000    4    3    2
2012-01-02 00:00:00.000    2    2    2
2012-01-03 00:00:00.000    2    1    0
*/

------解决方案--------------------
同意一楼的！！

------解决方案--------------------
业务日期	工人	老师	学生
2012-01-01	3	2	4
2012-01-02	2	2	2
2012-01-03	1	0	2

------解决方案--------------------
--生成测试数据
CREATE TABLE [test]
([ywSJ] [datetime] NOT NULL ,
[XinXiLY] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL )

insert into test select '2012-01-01','学生'
insert into test select '2012-01-01','老师'  
insert into test select '2012-01-01','老师'  
insert into test select '2012-01-01','学生'  
insert into test select '2012-01-01','工人'  
insert into test select '2012-01-01','工人'  
insert into test select '2012-01-01','学生'