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
有数据的填充，无数据的用0表示，请问这句动态语句应该怎么写？
------解决方案--------------------
- SQL code
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 @[email protected]+',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 @[email protected]+'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)