这句动态语句应该怎么写？
tableA
Task字段里的B1和B2是tableB中的两个字段名称
tbAId tbAName tbAValue Task
1 A1 0 B1
2 A2 1 B1
3 A3 2 B1
4 A4 3 B1
5 A9 0 B2
6 A10 1 B2
7 A11 2 B2
tableB
tbBId B1(字段名称) B2(字段名称)
1 3 1
2 1 0
3 2 0
4 0 2
我想实现这样的效果：
A1 A2 A3 A4
A9 0 1 1 0
A10 0 0 0 1
A11 1 0 0 0
主要是看tableB中的数据，比如tableB中的第一条数据
tbBId B1 B2
1 3 1
根据B1和B1的数据3找到tableA中的数据
tbAId tbAName tbAValue Task
4 A4 3 B1
得出A4
根据B2和B2的数据1找到tableA中的数据
tbAId tbAName tbAValue Task
6 A10 1 B2
得出A10
那么根据tableB中的第一条数据
tbBId B1 B2
1 3 1
那么在A4和A10的交界点加1
A1 A2 A3 A4
A9 0 0 0 0
A10 0 0 0 1
A11 0 0 0 0
假如tableB中有两条
tbBId B1 B2
1 3 1
这样的数据
那么最终显示的效果是
A1 A2 A3 A4
A9 0 0 0 0
A10 0 0 0 2
A11 0 0 0 0
其他数据以此类推
以下Sql语句对于B1,B2只能做到有则显示，无则不显示，现在我想假如tableB中没有B1,B2的数据，在最终显示时也显示出来
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 tablebselect 3, 1 union allselect 1, 0 union allselect 2 ,0 union allselect 0, 2declare @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 @SQLSET @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 @SQLEXEC (@SQL)
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 tbaname as b1name from tablea where task='b1')A )b--PRINT @SQLSET @SQL=@SQL+'FROM (select tbaname as b2name,b1name from (select * from tablea where task=''b2'') b left join (select b1,(select tbAName from tablea where a.b1=tbAValue and task=''B1'') as b1name,b2 from tableb a)c on b.tbavalue=c.b2)E GROUP BY B2NAME ORDER BY B2NAME DESC'PRINT @SQLEXEC (@SQL)
