这样的sql语句应该怎么写？各位大侠 谢谢了
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','学生'
insert into test select '2012-01-01','工人'
insert into test select '2012-01-01','学生'

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




select * from test



select convert(char(10),ywsj,120) as 业务日期,ISNULL(XinXiLY,'') AS 来源途径,count(*) as 人数
from test where convert(char(10),ywsj,120) between '2012-01-01' and '2012-01-03'
GROUP BY ISNULL(XinXiLY,''),convert(char(10),ywsj,120)


我想得到的结果是
业务日期 工人 老师 学生
2011-01-01 3 2 4
2011-01-02 2 2 2
2011-01-03 1 0 2

要得到这样的结果 语句该怎么写啊？



[解决办法]

SQL code

select ywsj,sum(case XinXiLY when '学生' then 1 else 0 end) [学生],            sum(...) [],            sum(...) []from tbgroup 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) ppivot( count(Xinxily) for XinxiLy in ([学生],[工人],[老师]))as pvt/*ywSJ    学生    工人    老师2012-01-01 00:00:00.000    4    3    22012-01-02 00:00:00.000    2    2    22012-01-03 00:00:00.000    2    1    0*/
[解决办法]
同意一楼的！！
[解决办法]
业务日期工人老师学生
2012-01-01324
2012-01-02222
2012-01-03102
[解决办法]
--生成测试数据
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','学生'   
 
insert into test select '2012-01-01','工人'  
insert into test select '2012-01-01','学生'

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



select * from test


--
select convert(char(10),ywsj,120) as 业务日期,ISNULL(XinXiLY,'') AS 来源途径,count(*) as 人数 
from test where convert(char(10),ywsj,120) between '2012-01-01' and '2012-01-03'
GROUP BY ISNULL(XinXiLY,''),convert(char(10),ywsj,120)

-----------------------------------------------------
declare @str varchar(1000)
set @str=''
select @str=@str+','+[XinXiLY]+
'=sum(case when [XinXiLY]='+QUOTENAME([XinXiLY],'''')+'then 1 else 0 end)'
from [test] group by [XinXiLY]
print @str
set @str='select convert(char(10),ywsj,120) as 业务日期'+@str+' from [test] group by [ywSJ]'
print @str
exec(@str)
-----------------------------------------------------
/*
--我想得到的结果是
业务日期工人老师学生
2012-01-01324
2012-01-02222
2012-01-03102
*/
drop table [test]
[解决办法]
探讨


CREATE TABLE [zd_test] (
[MingCheng] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
GO

xinxily字段的值来源于表zd_test 名称
可能会有不少值，按楼上方法写有些不灵活，能否有更好的方法实现呢？谢谢
insert into zd_test sel……

[解决办法]
一楼的结果 是正确的，二楼的那个select * from
(select * from test) p
pivot
(
  count(Xinxily)
  for XinxiLy in ([学生],[工人],[老师])
)as pvt

pivot是什么意思啊，表名？
[解决办法]
探讨

一楼的结果 是正确的，二楼的那个select * from
(select * from test) p
pivot
(
 count(Xinxily)
 for XinxiLy in ([学生],[工人],[老师])
)as pvt

pivot是什么意思啊，表名？

[解决办法]
顶1楼，这方法简单有效。