代码如下
declare @sql varchar(8000)
set @sql = 'select CompanyID '
select @sql = @sql + ' , max(case EquipName when ''' + EquipName + ''' then number else 0 end) [' + EquipName + ']'
from (select distinct EquipName from (select CompanyID,count(EquipName) number,EquipName from WL_EquipmentView
group by EquipName,CompanyID) b) as a
set @sql = @sql + ', sum(number) total from (select CompanyID,count(EquipName) number,EquipName from WL_EquipmentView where state=1 and BtypeCode=2
group by EquipName,CompanyID) c group by CompanyID '
set @sql = 'select * from ('+@sql+') aa full join
(select CompanyID,
max(case EquipName when ''计算机'' then number else 0 end) 计算机,
max(case EquipName when ''集中存储'' then number else 0 end) 集中存储,
max(case EquipName when ''服务器'' then number else 0 end) 服务器,
sum(number) totalCom
from (select CompanyID,count(EquipName)
number,EquipName from ComputerInfoView
group by EquipName,CompanyID) c group by CompanyID ) bb on aa.CompanyID=bb.CompanyID'
print (@sql)
exec(@sql)用存储过程行不行
declare @sql varchar(8000)
set @sql = 'select CompanyID '
select @sql = @sql + ' , max(case EquipName when ''' + EquipName + ''' then number else 0 end) [' + EquipName + ']'
from (select distinct EquipName from (select CompanyID,count(EquipName) number,EquipName from WL_EquipmentView
group by EquipName,CompanyID) b) as a
set @sql = @sql + ', sum(number) total from (select CompanyID,count(EquipName) number,EquipName from WL_EquipmentView where state=1 and BtypeCode=2
group by EquipName,CompanyID) c group by CompanyID '
set @sql = 'select * from ('+@sql+') aa full join
(select CompanyID,
max(case EquipName when ''计算机'' then number else 0 end) 计算机,
max(case EquipName when ''集中存储'' then number else 0 end) 集中存储,
max(case EquipName when ''服务器'' then number else 0 end) 服务器,
sum(number) totalCom
from (select CompanyID,count(EquipName)
number,EquipName from ComputerInfoView
group by EquipName,CompanyID) c group by CompanyID ) bb on aa.CompanyID=bb.CompanyID'
print (@sql)
exec(@sql)用存储过程行不行
解决方案 »
- SQL查询,主表记录,在子表中是否有记录的标记
- 求一条大容量数据查询语句
- 简单求重复记录
- 求一个简单SQl语句,高手指点阿~~~
- SSRS报表谁做过啊,请教一个分页的问题
- 如何把同一台机器上的有相同结构的不同数据库A,B全部实时放入另一数据库C中,进来有分数!!谢谢!!!
- 求一个SQL行列转换的解决方案
- 查询分析器,输入ip连不上数据库,但输入机器名却能连上,这是怎么回事????
- javax.servlet.ServletException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.的问题
- 如何把某个表的表结构变成sql语句,然后由另一个sql server来执行,生成相同的表!急!
- 一个复杂的查询,有耐心的看呀!
- 64位的Sql Server2008 在查询分析器里面可以读取excel文件吗?
As
Begin
<嵌入你的语法>
End
insert into 表 exec(@sql)
insert into #tb exec(@sql)
然后最后依据改为exec('Insert Into #T'+@sql)
关键是不知道新表的表结构啊 用select * into from 行不行啊
但是又报错
方法是exec('select * Into from T('+@sql+') as cc')
谢了