create table tableA
(

tbAId int identity(1,1),
tbAName varchar(10),
tbAValue int,
Task varchar(30)
)
insert into tableA 
select 'A1', 0 ,'B1' union all 
select 'A2', 1, 'B1'union all 
select 'A3', 2, 'B1'union all 
select 'A4', 3, 'B1'union all 
select 'A9',0, 'B2'union all 
select 'A10', 1, 'B2'union all 
select 'A11', 2, 'B2'
create table tableB(tbBId int identity(1,1),
b1 int,
b2 int)
insert into tableb
select  3, 1 union all
select   1, 0 union all
select   2 ,0 union all
select   0, 2
declare @sql varchar(max)
set @sql='select b2name'
select @sql=@sql+',SUM(case b1name when '''+b1name+''' then 1 else 0 end ) as ['+b1name+']'
from (SELECT DISTINCT B1NAME FROM (select b1,(select tbAName from tablea where a.b1=tbAValue and task='B1') as b1name,b2,(select tbAName from tablea where a.b2=tbAValue and task='B2') as b2name from tableb  a
)A )b
--PRINT @SQL
SET @SQL=@SQL+'FROM (select b1,(select tbAName from tablea where a.b1=tbAValue and task=''B1'') as b1name,b2,(select tbAName from tablea where a.b2=tbAValue and task=''B2'') as b2name from tableb  a
)B GROUP BY B2NAME ORDER BY B2NAME DESC'
PRINT @SQL
EXEC (@SQL)