创建交叉临时表

--使用临时表的行程汇总交叉表，并生成另外一个临时表
--选择嘉宾个人参与或嘉宾所属身份参与的相关活动
--创建t_staff、t_activity、t_activity_staff 关联临时表
select * into #aa from (
--选择出嘉宾参与的活动
select a.staff_id,a.staff_name,a.honour,a.order_name,b.activity_name,b.activity_id,b.begin_time
from t_staff a, t_activity b, t_activity_staff c
where 1=1
and a.staff_id = c.staff_id
and b.activity_id = c.activity_id
and b.conference_id=1
and b.activity_type<>4
--合并重复
union
--选择身份组参与的活动行程
select 
c.staff_id,d.staff_name,d.honour,d.order_name,a.activity_name,a.activity_id,a.begin_time
from t_activity a,t_activity_staff b,t_meeting_staff c,t_staff d

where 1=1
and a.activity_id = b.activity_id
and b.staff_id=0
and a.activity_type<>4
and b.staffgroup_id = c.group_id
and c.staff_id = d.staff_id
 ) as aa 
--select a.staff_id,a.staff_name,a.honour,a.order_name,a.activity_name,a.activity_id,a.begin_time
-- from #aa a
--group by a.activity_id,a.staff_id,a.staff_name,a.honour,a.order_name,a.activity_name,a.begin_time
--order by a.begin_time
--drop table #aa
--此处为用来select查询的语句，并行转列
 Declare @sql varchar(8000)
 set @sql='select * into #bb from(select staff_id,staff_name,honour,order_name'
 select @sql=@sql+',activity_id'+cast(activity_id as varchar)+'=max(case when activity_id='+ cast(activity_id as varchar) +' then ''1'' else '' '' end)' 
 from #aa group by activity_id,begin_time order by begin_time
 set @sql = @sql + ' from #aa group by staff_id,staff_name,honour,order_name ) as bb' 
--此处为用t_staff外链接临时表#bb，主要用来把没有参与活动的嘉宾也排列出来
 set @sql = @sql + ' select a.staff_id,a.staff_name,b.* from t_staff a,#bb b'
 set @sql = @sql + ' where a.conference_id=''1'''
        set @sql = @sql + '  and  a.staff_id *= b.staff_id'
 --set @sql = @sql + ' group by a.staff_id,a.staff_name,b.*'
        set @sql = @sql + '  drop table #bb'      
 exec (@sql ) 
GO
--删除临时表
drop table #aa
--end---