select 仓库名,类别
,sum(case when 领用单位='sam' then 领用金额 else 0 end) sam
,sum(case when 领用单位='sb' then 领用金额 else 0 end) sb
,sum(case when 领用单位='sbzb' then 领用金额 else 0 end) sbzb
,sum(case when 领用单位='zb' then 领用金额 else 0 end) zb
,sum(case when 领用单位='qq' then 领用金额 else 0 end) qq
from (你的查询语句)tem
group by 仓库名,类别
,sum(case when 领用单位='sam' then 领用金额 else 0 end) sam
,sum(case when 领用单位='sb' then 领用金额 else 0 end) sb
,sum(case when 领用单位='sbzb' then 领用金额 else 0 end) sbzb
,sum(case when 领用单位='zb' then 领用金额 else 0 end) zb
,sum(case when 领用单位='qq' then 领用金额 else 0 end) qq
from (你的查询语句)tem
group by 仓库名,类别
sum(case when export.PrimaryCo='sam' then export.PlannedPrice*export.ExportCount
end) as sam,
sum(case when export.PrimaryCo='sb' then export.PlannedPrice*export.ExportCount
end) as sb,
................
from export,warehouse,Location,EquipStructure,Equip where export.LocationID =Location.ID and export.equipID = equip.ID and equip.StructureID = EquipStructure.ID and Location.WarehouseID=warehouse.ID group by warehouse.Name,left(EquipStructure.EquipType,4),export.PrimaryCo
sum(case when export.PrimaryCo='sam' then export.PlannedPrice*export.ExportCount
end) as sam,
sum(case when export.PrimaryCo='sb' then export.PlannedPrice*export.ExportCount
end) as sb,
................
from export,warehouse,Location,EquipStructure,Equip where export.LocationID =Location.ID and export.equipID = equip.ID and equip.StructureID = EquipStructure.ID and Location.WarehouseID=warehouse.ID group by warehouse.Name,left(EquipStructure.EquipType,4)
,sum(case when 领用单位='sam' then 领用金额 else 0 end) sam
,sum(case when 领用单位='sb' then 领用金额 else 0 end) sb
,sum(case when 领用单位='sbzb' then 领用金额 else 0 end) sbzb
,sum(case when 领用单位='zb' then 领用金额 else 0 end) zb
,sum(case when 领用单位='qq' then 领用金额 else 0 end) qq
from (你的查询语句)tem
group by 仓库名,类别
OR:
declare @sql varchar(8000)
set @sql = 'select 仓库名,类别'
select @sql = @sql + ',sum(case a when 领用单位='''+primaryCo+'''
then 领用金额 else 0 end) '+primaryCo
from CoOrgselect @sql = @sql+ ' from (你的查询语句)tem group by 仓库名,类别'exec(@sql)
case left(EquipStructure.EquipType,3) when '刀工具' then left(EquipStructure.EquipType,3) else left(EquipStructure.EquipType,4) end as 类别,
sum(case when export.PrimaryCo='sam' then export.PlannedPrice*export.ExportCount
end) as sam,
sum(case when export.PrimaryCo='sb' then export.PlannedPrice*export.ExportCount
end) as sb,
................
from export,warehouse,Location,EquipStructure,Equip where export.LocationID =Location.ID and export.equipID = equip.ID and equip.StructureID = EquipStructure.ID and Location.WarehouseID=warehouse.ID group by warehouse.Name,
case left(EquipStructure.EquipType,3) when '刀工具' then left(EquipStructure.EquipType,3) else left(EquipStructure.EquipType,4) end
select 仓库名,类别
,sum(case when 领用单位='sam' then 领用金额 else 0 end) sam
,sum(case when 领用单位='sb' then 领用金额 else 0 end) sb
,sum(case when 领用单位='sbzb' then 领用金额 else 0 end) sbzb
,sum(case when 领用单位='zb' then 领用金额 else 0 end) zb
,sum(case when 领用单位='qq' then 领用金额 else 0 end) qq
from (你的查询语句)tem
group by 仓库名,类别
上面??????
declare @sql varchar(8000)
set @sql = 'select 仓库名,类别'
select @sql = @sql + ',sum(case a when 领用单位='''+primaryCo+'''
then 领用金额 else 0 end) '+primaryCo
from CoOrgselect @sql = @sql+ ' from (你的查询语句)tem group by 仓库名,类别'exec(@sql)
set @sql = 'select warehouse.Name,
case left(EquipStructure.EquipType,3) when '刀工具' then left(EquipStructure.EquipType,3) else left(EquipStructure.EquipType,4) end as 类别'select @sql = @sql + ',sum(case a when export.PrimaryCo='''+primaryCo+'''
then export.PlannedPrice*export.ExportCount else 0 end) '+primaryCo
from CoOrgselect @sql = @sql+ ' from export,warehouse,Location,EquipStructure,Equip where export.LocationID =Location.ID and export.equipID = equip.ID and equip.StructureID = EquipStructure.ID and Location.WarehouseID=warehouse.ID group by warehouse.Name,
case left(EquipStructure.EquipType,3) when '刀工具' then left(EquipStructure.EquipType,3) else left(EquipStructure.EquipType,4) end'exec(@sql)
declare @sql varchar(8000)
set @sql = 'select 仓库名,类别'
select @sql = @sql + ',sum(case a when 领用单位='''+primaryCo+'''
then 领用金额 else 0 end) '+primaryCo
from CoOrgselect @sql = @sql+ ' from (你的查询语句)tem group by 仓库名,类别'exec(@sql)
这段代码不能执行,怎样才能使查询出来的领用单位和交叉表查询配合
产生领用单位的查询:
select primaryCo from CoOrg where SecondaryCo is null非常感谢!!!!
TRY:declare @sql varchar(8000)
set @sql = 'select 仓库名,类别'
select @sql = @sql + ',sum(case when 领用单位='''+primaryCo+'''
then 领用金额 else 0 end) '+primaryCo
from CoOrg where SecondaryCo is nullselect @sql = @sql+ ' from (你的查询语句)tem group by 仓库名,类别'
print @sql ---看看这个语句和我第一个回复的语句有没有差别.:)
exec(@sql)
非常感谢你,我知道了什么方面的原因!!!!