-->try
create table tb(f1 int,type varchar(1))
insert into tb
select 1,'A' union all
select 2,'B' union all
select 3,'C'
create table type_A(col1 int)
insert into type_A
select 1 union all
select 2
create table type_B(col1 int)
insert into type_B
select 1 union all
select 2
create table type_C(col1 int)
insert into type_C
select 1 union all
select 2
go
declare @sql varchar(4000)
select @sql=isnull(@sql+' union all ','')+'select count(*) as cnt,'''+type+''' as type from type_'+type
from tb
set @sql='select tb.*,t.cnt from tb,('+@sql+')t where tb.type=t.type'
--print @sql
exec(@sql)
/*
f1          type cnt
----------- ---- -----------
1           A    2
2           B    2
3           C    2
*/