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

[esft]

已知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语句

[叶子]

create table TabelA (Name varchar(5),GoToPlace varchar(7),Event varchar(8),Date datetime)
insert into TabelA
select 'Herry','China','Shopping','2009-10-1' union all
select 'John','Japan','Work','2009-10-3' union all
select 'Jack','America','Study','2003-12-1' union all
select 'Tom','India','Work','2003-1-1' union all
select 'Herry','America','Shopping','2004-10-1' union all
select 'John','Japan','Study','2005-10-3'

select * from TabelA


declare @sql varchar(8000)
set @sql='select Event,[Sum]=count(1)'

select @sql=@sql+',['+GoToPlace+']=sum(case GoToPlace when '''+GoToPlace+''' then 1 else 0 end)'
from (select distinct GoToPlace from TabelA) t

set @sql=@sql+' from TabelA group by Event'
exec(@sql)
/*
Event    Sum         America     China       India       Japan
-------- ----------- ----------- ----------- ----------- -----------
Shopping 2           1           1           0           0
Study    2           1           0           0           1
Work     2           0           0           1           1
*/

[肥龙上天]

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Name] nvarchar(5),[GoToPlace] nvarchar(7),[Event] nvarchar(8),[Date] Datetime)
Insert #T
select N'Herry',N'China',N'Shopping','2009-10-1' union all
select N'John',N'Japan',N'Work','2009-10-3' union all
select N'Jack',N'America',N'Study','2003-12-1' union all
select N'Tom',N'India',N'Work','2003-1-1' union all
select N'Herry',N'America',N'Shopping','2004-10-1' union all
select N'John',N'Japan',N'Study','2005-10-3'
Go

declare @s nvarchar(max)
set @s = 'select * from (select name,event,gotoplace from #t) a
pivot ( count(name) for gotoplace in( '
select @s = @s + stuff((select ','+gotoplace from (select distinct gotoplace from #t) t for xml path('')),1,1,'')
set @s = @s +' ))b'
exec(@s)

event    America     China       India       Japan
-------- ----------- ----------- ----------- -----------
Shopping 1           1           0           0
Study    1           0           0           1
Work     0           0           1           1

(3 row(s) affected)

[claro]

帮顶。

[ChinaJiaBing]

declare @table table (name nvarchar(10),gotoplace nvarchar(10),event nvarchar(10),[date] datetime)
insert into @table  select 'herry','china','shopping','2009-10-1'
    union all select 'john','japan','work','2009-10-3'
    union all select 'jack','America','study','2003-12-1'
    union all select 'tom','india','work','2003-1-1'
    union all select 'herry','america','shopping','2004-10-1'
    union all select 'john','japan','study','2005-10-3'

select event,[china]= sum(case when gotoplace='china' then 1 else 0 end),
             [japan]= sum(case when gotoplace='japan' then 1 else 0 end), 
             [America]= sum(case when gotoplace='America' then 1 else 0 end),
             [America]=sum(case when gotoplace='herry' then 1 else 0 end)
             from @table group by [event]
event      china       japan       America     America
---------- ----------- ----------- ----------- -----------
shopping   1           0           1           0
study      0           1           1           0
work       0           1           0           0

(3 行受影响)

[LIHY70]

路过，学习~~~

[yeaisheng]

恩，感谢~~~

[中国风]

use Tempdb
go
--> --> 

 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([Name] nvarchar(5),[GoToPlace] nvarchar(7),[Event] nvarchar(8),[Date] Datetime)
Insert #T
select N'Herry',N'China',N'Shopping','2009-10-1' union all
select N'John',N'Japan',N'Work','2009-10-3' union all
select N'Jack',N'America',N'Study','2003-12-1' union all
select N'Tom',N'India',N'Work','2003-1-1' union all
select N'Herry',N'America',N'Shopping','2004-10-1' union all
select N'John',N'Japan',N'Study','2005-10-3'
Go
declare @s nvarchar(1000)
set @s='select [Event]'
Select @s=@s+','+quotename([GoToPlace])+'=sum(case when [GoToPlace]='+quotename([GoToPlace],'''')+' then 1 else 0 end)' from #T group by [GoToPlace]
exec(@s+' from #T group by [Event]')



(6 個資料列受到影響)
Event    America     China       India       Japan
-------- ----------- ----------- ----------- -----------
Shopping 1           1           0           0
Study    1           0           0           1
Work     0           0           1           1

(3 個資料列受到影響)

[中国风]

其它方法參照

http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

[子陌红尘]

declare @sql varchar(8000)
set @sql='select Event,[Sum]=count(1)'

select @sql=@sql+',['+GoToPlace+']=sum(case GoToPlace when '''+GoToPlace+''' then 1 else 0 end)'
from (select distinct GoToPlace from TableA) t

set @sql=@sql+' from TableA group by Event'
exec(@sql)