/*
create table tableA(id int, state int, type1 int, type2 int, type3 int)
create table tbState(id int, StateName varchar(100))
create table tbType(id int, TypeName varchar(100), parentId int, TypeCorresponding varchar(100))
*/
GO
/*
insert into tableA values(1, 3, 2, 4, 8)
insert into tableA values(2, 1, 1, 3, 5)
insert into tableA values(3, 3, 1, 3, 5)
insert into tableA values(4, 2, 1, 3, 6)
insert into tableA values(5, 1, 2, 4, 8)
insert into tableA values(6, 1, 2, 4, 8)
insert into tableA values(7, 2, 2, 4, 8)

insert into tbState values(1, 'StateA')
insert into tbState values(2, 'StateB')
insert into tbState values(3, 'StateC')

insert into tbType values(1, 'TypeA', 0, 'TypeH1')
insert into tbType values(2, 'TypeB', 0, 'TypeH1')
insert into tbType values(3, 'TypeAA', 1, 'TypeH2')
insert into tbType values(4, 'TypeBA', 2, 'TypeH2')
insert into tbType values(5, 'TypeAAA', 3, 'TypeH3')
insert into tbType values(6, 'TypeAAB', 3, 'TypeH3')
insert into tbType values(7, 'TypeBAA', 4, 'TypeH3')
insert into tbType values(8, 'TypeBAB', 4, 'TypeH3')

*/

--传入参数@p, 值取 tyType.id, 例如想传入TypeA, 则@p = 1
declare @p int
set @p = 1

declare @s varchar(4000), @s1 varchar(4000)
set @s = ''

set @s1 = 'select ts.StateName, tt.typeName, Count(ta.id) cnt
into #a
from tbState ts 
    full join (select * from tbType tt where tt.ParentId = ' + cast(@p as varchar) + ') tt on 1=1
    left join tableA ta on ta.state = ts.id 
        and (ta.Type1 = tt.id or ta.Type2 = tt.id or ta.Type3 = tt.id)
group by ts.StateName, tt.TypeName
'

select @s = @s + 'Sum(case when StateName = ' + char(39) + StateName + char(39) + ' then cnt else 0 end) ' + StateName + ','
from tbState
if len(@s) <> 0 set @s = left(@s, len(@s) - 1)

Set @s = @s1 + '
' + 'select typeName, ' + @s + ' from #a group by typeName order by typeName'

print @s
exec(@s)

------解决方案--------------------
数据库是2000的话，那就用函数代替
SQL code

CREATE FUNCTION f_Cid(@TypeName VARCHAR(50)) 
RETURNS @t TABLE(ID INT,Level int) 
AS 
BEGIN 
    DECLARE @ID INT
    DECLARE @Level int 
    SET @Level=1 
    INSERT @t SELECT Id,@Level  FROM tbType WHERE tbType.TypeName=@TypeName
    WHILE @@ROWCOUNT>0 
    BEGIN 
        SET @Level=@Level+1 
        INSERT @t SELECT a.Id,@Level