100分求教一个Sql 统计查询语句

来源：百度知道 编辑：UC知道 时间：2024/05/31 02:34:28
知TabelA结构为
Name GoToPlace Event Date
Herry China Shopping 2009-10-1
John Japan Work 2009-10-3
Jack America Study 2003-12-1
Tom India Work 2003-1-1
Herry America Shopping 2004-10-1
John Japan Study 2005-10-3

实际中GoToPlace和Event都在10种以上
现要由TableA统计去某地做某事的人次数量
最终得到TableB

Sum China Japan America India
Shopping 2 1 0 1 0
Work 2 0 1 0 1
Study 2 0 1 1 0

跪求此Sql语句

动态脚本，不管你有多少项

declare @sql varchar(8000)
set @sql = 'select Event,'
select @sql = @sql+
' sum(case when GoToPlace= '''+GoToPlace+''' then 1 else 0 end) as ['+GoToPlace+'],'
From (Select Distinct GoToPlace From TableA) T
set @sql = left(@sql,len(@sql)-1)+' from TableA group by Event'
exec(@sql)

lzp4881 少写了 end

select Event,
sum(case when GoToPlace='China' then 1 else 0 end) as China,
sum(case when GoToPlace='Japan' then 1 else 0 end) as Japan,
sum(case when GoToPlace='America' then 1 else 0 end) as America,
sum(case when GoToPlace='India' then 1 else 0 end) as India
from TableA
group by Event

存储过程这个方法不错哦

select Gotoplace,Event ,count(*) from #T1
group by Gotoplace,Event

将就着用吧

select Event Sum,
Count(case GoToPlace when 'China' then 1 end ) as China,
Count(ca