DECLARE @s VARCHAR(8000)
SET @s = ''SELECT
@s = @s + ','
+ InventoryClass.cInvCName+'=SUM(CASE WHEN InventoryClass.cInvCName='''
+ InventoryClass.InventoryClass+''' THEN DispatchLists.iQuantity END)'
FROM
DispatchLists,
DispatchList,
Department,
Inventory,
InventoryClass
where
DispatchLists.DLID = DispatchList.DLID
and
DispatchList.cDepCode = Department.cDepCode
and
DispatchLists.cInvCode = Inventory.cInvCode
and
DispatchLists.cInvCode = Inventory.cInvCode
and
Inventory.cInvCCode = InventoryClass.cInvCCode
GROUP BY
InventoryClass.cInvCName
SET @s = ' SELECT Department.cDepName'+@s
+' FROM
+' DispatchLists,+
+' DispatchList,'
+' Department, '
+' Inventory, '
+' InventoryClass'
+' WHERE '
+' DispatchLists.DLID = DispatchList.DLID '
+' and '
+' DispatchList.cDepCode = Department.cDepCode'
+' and '
+' DispatchLists.cInvCode = Inventory.cInvCode '
+' and '
+' DispatchLists.cInvCode = Inventory.cInvCode '
+' and '
+' Inventory.cInvCCode = InventoryClass.cInvCCode '
+' GROUP BY Department.cDepName'EXEC(@s)
SET @s = ''SELECT
@s = @s + ','
+ InventoryClass.cInvCName+'=SUM(CASE WHEN InventoryClass.cInvCName='''
+ InventoryClass.InventoryClass+''' THEN DispatchLists.iQuantity END)'
FROM
DispatchLists,
DispatchList,
Department,
Inventory,
InventoryClass
where
DispatchLists.DLID = DispatchList.DLID
and
DispatchList.cDepCode = Department.cDepCode
and
DispatchLists.cInvCode = Inventory.cInvCode
and
DispatchLists.cInvCode = Inventory.cInvCode
and
Inventory.cInvCCode = InventoryClass.cInvCCode
GROUP BY
InventoryClass.cInvCName
SET @s = ' SELECT Department.cDepName'+@s
+' FROM
+' DispatchLists,+
+' DispatchList,'
+' Department, '
+' Inventory, '
+' InventoryClass'
+' WHERE '
+' DispatchLists.DLID = DispatchList.DLID '
+' and '
+' DispatchList.cDepCode = Department.cDepCode'
+' and '
+' DispatchLists.cInvCode = Inventory.cInvCode '
+' and '
+' DispatchLists.cInvCode = Inventory.cInvCode '
+' and '
+' Inventory.cInvCCode = InventoryClass.cInvCCode '
+' GROUP BY Department.cDepName'EXEC(@s)
SET @s = ''SELECT
@s = @s + ','
+ InventoryClass.cInvCName+'=SUM(CASE WHEN InventoryClass.cInvCName='''
+ InventoryClass.InventoryClass+''' THEN DispatchLists.iQuantity END)'
FROM
DispatchLists,
DispatchList,
Department,
Inventory,
InventoryClass
where
DispatchLists.DLID = DispatchList.DLID
and
DispatchList.cDepCode = Department.cDepCode
and
DispatchLists.cInvCode = Inventory.cInvCode
and
Inventory.cInvCCode = InventoryClass.cInvCCode
GROUP BY
InventoryClass.cInvCName
SET @s = ' SELECT Department.cDepName'+@s
+' FROM
+' DispatchLists, '
+' DispatchList, '
+' Department, '
+' Inventory, '
+' InventoryClass '
+' WHERE '
+' DispatchLists.DLID = DispatchList.DLID '
+' and '
+' DispatchList.cDepCode = Department.cDepCode'
+' and '
+' DispatchLists.cInvCode = Inventory.cInvCode '
+' and '
+' Inventory.cInvCCode = InventoryClass.cInvCCode '
+' GROUP BY Department.cDepName'EXEC(@s)
Create table T (型号 varchar(5),区域 varchar(10), 数量 int)
insert into T select 'A1','北京',50
union all select 'B1','北京',100
union all select 'B1','上海',90
union all select 'C1','南京',60
union all select 'A2','重庆',80
union all select 'A3','武汉',70
union all select 'A3','长沙',100
union all select 'C2','长沙',90--动态SQL
declare @s varchar(8000)
set @s='select 区域 '
select @s=@s+',['+型号+']=sum(case when 型号='''+型号+''' then 数量 else 0 end)'
from T
group by 型号
set @s=@s+' from T group by 区域'
exec(@s)--结果
区域 A1 A2 A3 B1 C1 C2
---------- ----------- ----------- ----------- ----------- ----------- -----------
北京 50 0 0 100 0 0
长沙 0 0 100 0 0 90
南京 0 0 0 0 60 0
上海 0 0 0 90 0 0
武汉 0 0 70 0 0 0
重庆 0 80 0 0 0 0
drop table T
--或者你把原始表,贴出来!^_^
+' FROM --这一行遗漏了单引号
+' DispatchLists,+ --这一行将单引号写成了加号
修改如下:SET @s = ' SELECT Department.cDepName'+@s
+' FROM '
+' DispatchLists,'
----------------------------------------------------------------------------
DECLARE @s VARCHAR(8000)
SET @s = ''SELECT
@s = @s + ','
+ InventoryClass.cInvCName + '=SUM(CASE WHEN InventoryClass.cInvCName='''
+ InventoryClass.InventoryClass + ''' THEN DispatchLists.iQuantity END)'
FROM
DispatchLists,
DispatchList,
Department,
Inventory,
InventoryClass
where
DispatchLists.DLID = DispatchList.DLID
and
DispatchList.cDepCode = Department.cDepCode
and
DispatchLists.cInvCode = Inventory.cInvCode
and
Inventory.cInvCCode = InventoryClass.cInvCCode
GROUP BY
InventoryClass.cInvCName
SET @s = ' SELECT Department.cDepName' + @s
+' FROM '
+' DispatchLists, '
+' DispatchList, '
+' Department, '
+' Inventory, '
+' InventoryClass '
+' WHERE '
+' DispatchLists.DLID = DispatchList.DLID '
+' and '
+' DispatchList.cDepCode = Department.cDepCode'
+' and '
+' DispatchLists.cInvCode = Inventory.cInvCode '
+' and '
+' Inventory.cInvCCode = InventoryClass.cInvCCode '
+' GROUP BY Department.cDepName'EXEC(@s)
有错!!!!!!!!!
set @s='select isnull(Department.cDepName,''合计'') as 区域'
select @s=@s+',['+InventoryClass.cInvCName+']=sum(case when InventoryClass.cInvCName='''+InventoryClass.cInvCName+'''
then DispatchLists.iQuantity else 0 end)'
from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where DispatchLists.DLID = DispatchList.DLID and
DispatchList.cDepCode = Department.cDepCode and
DispatchLists.cInvCode = Inventory.cInvCode and
Inventory.cInvCCode = InventoryClass.cInvCCode
and Inventoryclass.cInvccode like '010200%'
group by InventoryClass.cInvCName
set @s=@s+' from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where Department.cdepname like ''%办事处%'' and Department.cDepCode like ''02%''
and DispatchList.dDate >=''8/1/2005''
and DispatchList.dDate <=''8/9/2005''
and DispatchLists.DLID = DispatchList.DLID
and DispatchList.cDepCode = Department.cDepCode
and DispatchLists.cInvCode = Inventory.cInvCode
and DispatchLists.cInvCode = Inventory.cInvCode
and Inventory.cInvCCode = InventoryClass.cInvCCode
group by Department.cDepName with rollup'
exec(@s)