CREATE TABLE #DEMO(athleteName VARCHAR(100),fullName VARCHAR(100),itemName varchar(100),athleteId varchar(100) ,id varchar(100))

insert into #DEMO
select '张三','高中男子甲组','100米','40','1'
union all
select '张三','高中男子甲组','110米栏','40','6'
union all
select '张三','高中男子甲组','1500米','40','7'
union all
select '李四','高中男子甲组','110米栏','41','8'
union all
select '李四','高中男子甲组','110米栏','41','9'
union all
select '王五','高中男子甲组','100米','42','10'
union all
select '王五','高中男子甲组','110米栏','42','11'
union all
select '赵六','高中男子甲组','100米','43','12'
union all
select '赵六','高中男子甲组','1500米','43','13'
union all
select '孙七','高中男子甲组','100米','44','14'


SELECT * FROM #DEMO

DECLARE @sql varchar(8000)
set @sql = '';

select @sql = @sql + ' (select count(1) from #DEMO A INNER JOIN (SELECT athleteName FROM #DEMO WHERE itemName=''' 
    + itemName + ''' GROUP BY athleteName ) B ON A.athleteName=B.athleteName AND A.itemName=t1.itemName ) AS ['+itemName+'], '
from #DEMO
group by itemName


set @sql = 'select itemName as [项目],'+ @sql +' count(*) as [项目报名人数]
from #DEMO t1
group by itemName '

print @sql
EXEC (@sql)

drop table #DEMO

------解决方案--------------------

SQL code
select a.itemname
,sum(case when b.itemname = '100米' then 1 else 0 end) as [100米]
,sum(case when b.itemname = '100米栏' then 1 else 0 end) as [100米栏]
,sum(case when b.itemname = '1500米' then 1 else 0 end) as [1500米]
from tab a inner join tab b
on a.athletename = b.athletename
group by a.itemname

------解决方案--------------------
SQL code
CREATE TABLE #DEMO(athleteName VARCHAR(100),fullName VARCHAR(100),itemName varchar(100),athleteId varchar(100) ,id varchar(100))

insert into #DEMO
select '张三','高中男子甲组','100米','40','1'
union all
select '张三','高中男子甲组','110米栏','40','6'
union all
select '张三','高中男子甲组','1500米','40','7'
union all
select '李四','高中男子甲组','100米','41','8'
union all
select '李四','高中男子甲组','110米栏','41','9'
union all
select '王五','高中男子甲组','100米','42','10'
union all
select '王五','高中男子甲组','110米栏','42','11'
union all
select '赵六','高中男子甲组','100米','43','12'
union all
select '赵六','高中男子甲组','1500米','43','13'
union all
select '孙七','高中男子甲组','100米','44','14'


DECLARE @sql varchar(8000)
set @sql = '';

select @sql = @sql + ',sum(case when b.itemname = ''' + itemName + ''' then 1 else 0 end) as [' + itemName + ']' 
from #DEMO
group by itemName


set @sql = 'select a.itemName as [项目]'+ @sql +',count(distinct a.athletename) as [项目报名人数]
from #DEMO a,#DEMO b
where a.athletename = b.athletename
group by a.itemName '

print @sql
EXEC (@sql)

drop table #DEMO

--结果
项目    100米    110米栏    1500米    项目报名人数
100米    5    3    2    5
110米栏    3    3    1    3
1500米    2    1    2    2

------解决方案--------------------
不行，视图不支持 EXEC 语句，最好用 存储过程，这样好扩展

------解决方案--------------------
加参数自己修改哦，原理一样的

视图不是好东西，这里也用不了