动态得到，但是没有链接成一个表
create table tb(y int,m int ,d int,w int,gw1 varchar(10),gw2 varchar(10), gw3 varchar(10))
insert tb
select 2007,2,3,1,'黄','王','李' union all
select 2007,2,4,2,'张','张','张' union all
select 2007,2,8,6,'王','黄','张' union all
select 2007,3,1,4,'张','王','黄' union all
select 2007,3,4,7,'李','张','黄' union all
select 2007,4,2,1,'张','黄','黄' union all
select 2007,4,3,2,'黄','王','王' union all
select 2007,5,1,2,'黄','张','张' union all
select 2007,5,2,3,'王','张','王' union all
select 2007,5,3,4,'李','王','李' union all
select 2007,5,4,5,'王','黄','黄' union all
select 2007,5,5,6,'张','王','黄' union all
select 2007,5,6,7,'黄','张','王' union all
select 2007,5,7,1,'张','张','张' union all
select 2007,6,7,1,'王','黄','李'

select distinct gw as [name], identity(int,1,1) as id into #tempTable from
      (select gw1 as gw from tb union  all select gw2 as gw from tb union all select gw3 as gw from tb) as t

declare @i int
set @i=(select count(*) from #tempTable)
declare @k int
set @k=1
declare @sql varchar(500) 
while @i>=@k
  begin   
     set @sql = 'Select Top 1 * into #tempTable' + convert(varchar(5),@k)+ ' From #tempTable Where id in (Select Top '+ convert(varchar(5),@k)+' id From #tempTable) Order by id desc' 
     set @sql = @sql +' select #tempTable'+ convert(varchar(5),@k)+ '.[name],  sum(case when gw1=#tempTable'+ convert(varchar(5),@k)+ '.[name] then 1 else 0 end) as gw1,sum(case when gw2=#tempTable'+ convert(varchar(5),@k)+ '.[name] then 1 else 0 end) as gw2,sum(case when gw3=#tempTable'+ convert(varchar(5),@k)+ '.[name] then 1 else 0 end) as gw3, sum(case when gw2=#tempTable'+ convert(varchar(5),@k)+ '.[name] and (w=6 or w=7) then 1 else 0 end) as '+ char(39)+'周六、日'+ char(39)+' from tb, #tempTable'+ convert(varchar(5),@k)+'  group by name   drop table #tempTable'+ convert(varchar(5),@k)
         set @k=@k+1
     exec(@sql)
  end
drop table tb,#tempTable


/*
name       gw1         gw2         gw3         周六、日
---------- ----------- ----------- ----------- -----------
黄          4           4           5           1


name       gw1         gw2         gw3         周六、日
---------- ----------- ----------- ----------- -----------
李          2           0           3           0


name       gw1         gw2         gw3         周六、日
---------- ----------- ----------- ----------- -----------
王          4           5           3           1

name       gw1         gw2         gw3         周六、日
---------- ----------- ----------- ----------- -----------
张          5           6           4           2
*/

------解决方案--------------------
借用楼上的数据:
create table tb(y int,m int ,d int,w int,gw1 varchar(10),gw2 varchar(10), gw3 varchar(10))
insert tb
select 2007,2,3,1,'黄','王','李' union all
select 2007,2,4,2,'张','张','张' union all
select 2007,2,8,6,'王','黄','张' union all
select 2007,3,1,4,'张','王','黄' union all
select 2007,3,4,7,'李','张','黄' union all
select 2007,4,2,1,'张','黄','黄' union all
select 2007,4,3,2,'黄','王','王' union all
select 2007,5,1,2,'黄','张','张' union all
select 2007,5,2,3,'王','张','王' union all
select 2007,5,3,4,'李','王','李' union all
select 2007,5,4,5,'王','黄','黄' union all
select 2007,5,5,6,'张','王','黄' union all
select 2007,5,6,7,'黄','张','王' union all
select 2007,5,7,1,'张','张','张' union all
select 2007,6,7,1,'王','黄','李'


select a.gw,isnull(a.gw1,0) gw1,isnull(b.gw2,0) gw2,isnull(c.gw3,0) gw3 ,i