首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

这句语句应该如何写

2012-04-25 
这句语句应该怎么写?tableATask字段里的B1和B2是tableB中的两个字段名称tbAId tbAName tbAValueTask1A10B1

这句语句应该怎么写?
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 @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) 

热点排行