create table hospital(hospitalid int,hospitalname varchar(10))
create table illType(illtypeID int,illTypeName varchar(10))
create table patient(id int, hospitalid int,illtypeID int)
insert into hospital values(1 , '医院1')
insert into hospital values(2 , '医院2')
insert into illType values(1,'病种1')
insert into illType values(2,'病种2')
insert into illType values(3,'病种3')
insert into patient values(1,1,1)
insert into patient values(2,1,2)
insert into patient values(3,1,3)
insert into patient values(4,2,1)
insert into patient values(5,2,2)
go

declare @sql varchar(8000)
set @sql = 'select i.illTypeName '
select @sql = @sql + ',sum(case h.hospitalname when ''' +  hospitalname + ''' then 1 else 0 end) [' + hospitalname + ']'
from (select h.hospitalname from hospital h) as a
set @sql = @sql + ' from hospital h ,illType i , patient p where h.hospitalid = p.hospitalid and p.illtypeID = i.illtypeID group by i.illTypeName'
exec(@sql) 

drop table hospital , illType,patient

/*
illTypeName 医院1         医院2         
----------- ----------- ----------- 
病种1         1           1
病种2         1           1
病种3         1           0
*/

------解决方案--------------------
SQL code
create table hospital(hospitalid int,hospitalname nvarchar(10))
insert into hospital select 1,'中心医院'
insert into hospital select 2,'郊区医院'
create table illType(illtypeID int,illTypeName nvarchar(10))
insert into illType select 1,'疾病A'
insert into illType select 2,'疾病B'
insert into illType select 3,'疾病C'
create table patient(id int,hospitalid int,illtypeID int)
insert into patient select 1,1,2
insert into patient select 2,1,3
insert into patient select 3,1,2
insert into patient select 4,2,1
insert into patient select 5,2,2
insert into patient select 6,2,2
go
declare @str1 nvarchar(4000),@str2 nvarchar(4000)  
select @str1=ISNULL(@str1+', ','')+'['+ltrim(hospitalid)+']',@str2=ISNULL(@str2+', ','')+'['+LTRIM(hospitalid)+']['+hospitalname+']' from hospital
exec('select illTypeName as 疾病种类,'+@str2+'
 from (select f.illTypeName,b.hospitalid from patient b inner join illType f on f.illtypeID=b.illtypeID)a
pivot
(count(hospitalid) for hospitalid in('+@str1+'))t')
/*
疾病种类       中心医院        郊区医院
---------- ----------- -----------
疾病A        0           1
疾病B        2           2
疾病C        1           0

(3 行受影响)

*/
go
drop table patient,illType,hospital