;with mycte as 
(
 select BName,Col,Sum(case when val='Y' then 1 else 0 end) cnt 
 from @T t
  CROSS APPLY (Values ('ARatingClass1',  ARatingClass1),
                      ('ARatingClass2',  ARatingClass2),
					  ('ARatingClass3',  ARatingClass3),
					  ('PRatingClass1',  PRatingClass1),
					  ('PRatingClass2',  PRatingClass2),
					  ('PRatingClass3',  PRatingClass3),
					  ('IRatingClass1',  IRatingClass1),
					  ('IRatingClass2',  IRatingClass2),
					  ('IRatingClass3',  IRatingClass3),
					  ('IRatingClass4',  IRatingClass4),
					  ('IRatingLimited', IRatingLimited),
					  ('FloatsLimited ', FloatsLimited),
					  ('RadioLimited',   RadioLimited))  d(Col,val) 
Group by Col,BName)
,cteUnDesignated as (
select 'UnDesignated' Col, count(distinct BName) cnt
from mycte
group by BName
Having(sum(cnt)=0)
)
,mycteFinal as(
select Col, Sum(cnt) cnt from mycte
group by Col
UNION ALL
select Col,sum(cnt) cnt from cteUnDesignated
group by Col
)
,myPivot as (
select Col,cnt,row_number()Over(partition by left(Col,len(Col)-1) Order by Right(Col,1))rn,
       LEFT(Col,patindex('%Class%',col)-1) as ClassName
from mycteFinal
where Col not like '%Limited'
union
select Col,cnt,5 as rn,LEFT(Col,patindex('%Limited%',col)-1) as ClassName
from mycteFinal
where Col like '%Limited'
)
Select
ISNULL(max(Case when rn=1 then Col else null end),'') 'Class 1',
ISNULL(max(Case when rn=1 then CAST(cnt AS VARCHAR(20)) else null end),'') 'Total',
ISNULL(max(Case when rn=2 then Col else null end),'') 'Class 2',
ISNULL(max(Case when rn=2 then CAST(cnt AS VARCHAR(20)) else null end),'') 'Total',
ISNULL(max(Case when rn=3 then Col else null end),'') 'Class 3',
ISNULL(max(Case when rn=3 then CAST(cnt AS VARCHAR(20)) else null end),'') 'Total',
ISNULL(max(Case when rn=4 then Col else null end),'') 'Class 4',
ISNULL(max(Case when rn=4 then CAST(cnt AS VARCHAR(20)) else null end),'') 'Total',
ISNULL(max(Case when rn=5 then Col else null end),'') 'Limited',
ISNULL(max(Case when rn=5 then CAST(cnt AS VARCHAR(20)) else null end),'') 'Total'
from mypivot
group by ClassName