--> 测试数据：[tableA]
if object_id('[tableA]') is not null drop table [tableA]
create table [tableA]([Id] int,[State] int,[Type] int)
insert [tableA]
select 1,3,4 union all
select 2,2,5 union all
select 3,1,2 union all
select 4,2,3 union all
select 5,3,3 union all
select 6,1,2 union all
select 7,2,1
--> 测试数据：[tbState]
if object_id('[tbState]') is not null drop table [tbState]
create table [tbState]([Id] int,[StateName] varchar(6))
insert [tbState]
select 1,'StateA' union all
select 2,'StateB' union all
select 3,'StateC'
--> 测试数据：[tbType]

if object_id('[tbType]') is not null drop table [tbType]
create table [tbType]([Id] int,[TypeName] varchar(20),[ParentId] INT)
insert INTO [tbType]
select 1,'TypeAAB',3 union all
select 2,'TypeAAA',3 union all
select 3,'TypeAA',0 union all
select 4,'TypeD',0 union all
select 5,'TypeE',0

declare @str varchar(max)
set @str=''
select 
    @str=@str+','+StateName+'=sum(case when StateName='
    +QUOTENAME(StateName,'''')+' then 1 else 0 end)'
from 
    (
    select 
a.Id,b.StateName,c.TypeName
from [tableA] a
left join [tbState] b
on a.State=b.Id
left join [tbType] c
on a.Type=c.Id
    )t
group by
    StateName
    
    
DECLARE @Type VARCHAR(10)
SET @Type = 'TypeAA'
    
exec('
;WITH List AS(
    SELECT tbType.* FROM tbType WHERE tbType.TypeName='''+@Type+'''
    UNION ALL
    SELECT tbType.* FROM List,tbType WHERE List.Id = tbType.ParentId 
)
select TypeName'+@str+' from ('+'
select 
a.Id,b.StateName,c.TypeName,c.Id as cid
from [tableA] a
left join [tbState] b
on a.State=b.Id
left join [tbType] c
on a.Type=c.Id
'+')t where  t.cid IN(select List.Id from List where List.TypeName<>'''+@Type+''') group by TypeName')


/*

(7 行受影响)

(3 行受影响)

(5 行受影响)
TypeName             StateA      StateB      StateC
-------------------- ----------- ----------- -----------
TypeAAA              2           0           0
TypeAAB              0           1           0

(2 行受影响)

*/