------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-08 17:42:32
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ORDER_NO nvarchar(7),CUST_NO nvarchar(3))
Go
Insert into ta
select 'A803001','ops' union all
select 'M803002','WWE'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(ORDER_NO nvarchar(7),ITEM nvarchar(2),PART_NO nvarchar(7),DIM1 int,UDIM1 nvarchar(2),DIM2 int,UDIM2 nvarchar(2))
Go
Insert into tB
select 'A803001','01','220-010',37,'''''',49,'''''' union all
select 'M803002','01','33W-001',550,'MM',250,'MM'
Go
-- Test Data: tC
If object_id('tC') is not null
Drop table tC
Go
Create table tC(ORDER_NO nvarchar(7),ITEM nvarchar(2),QTY int,Dt smalldatetime)
Go
Insert into tC
select 'A803001','01',20,'2008-03-07' union all
select 'M803002','01',30,'2008-05-30'
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+ltrim(id)+'月]=sum(case when datepart(mm,[dt]) = '+ltrim(id) + ' then qty* b.t else 0 end) '
from (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7
union select 8 union select 9 union select 10 union select 11 union select 12) a
--print @s
exec('select a.*,b.item,part_no ,'+@s+ ' from ta a left join
(
select ORDER_NO ,ITEM ,PART_NO,cast(case when udim1 = '''' then dim1 else dim1/25.4 end * case when udim2 = '''' then dim2 else dim2/25.4 end as numeric(12,2))as t
from tb) b
on a.order_no = b.order_no
left join tc c on a.order_no = c.order_no
group by a.ORDER_NO,a.CUST_NO,b.item,part_no')--Result:
/*ORDER_NO CUST_NO item part_no 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
-------- ------- ---- ------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
A803001 ops 01 220-010 .00 .00 56.20 .00 .00 .00 .00 .00 .00 .00 .00 .00
M803002 WWE 01 33W-001 .00 .00 .00 .00 6393.90 .00 .00 .00 .00 .00 .00 .00
*/
--End
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-08 17:42:32
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ORDER_NO nvarchar(7),CUST_NO nvarchar(3))
Go
Insert into ta
select 'A803001','ops' union all
select 'M803002','WWE'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(ORDER_NO nvarchar(7),ITEM nvarchar(2),PART_NO nvarchar(7),DIM1 int,UDIM1 nvarchar(2),DIM2 int,UDIM2 nvarchar(2))
Go
Insert into tB
select 'A803001','01','220-010',37,'''''',49,'''''' union all
select 'M803002','01','33W-001',550,'MM',250,'MM'
Go
-- Test Data: tC
If object_id('tC') is not null
Drop table tC
Go
Create table tC(ORDER_NO nvarchar(7),ITEM nvarchar(2),QTY int,Dt smalldatetime)
Go
Insert into tC
select 'A803001','01',20,'2008-03-07' union all
select 'M803002','01',30,'2008-05-30'
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+ltrim(id)+'月]=sum(case when datepart(mm,[dt]) = '+ltrim(id) + ' then qty* b.t else 0 end) '
from (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7
union select 8 union select 9 union select 10 union select 11 union select 12) a
--print @s
exec('select a.*,b.item,part_no ,'+@s+ ' from ta a left join
(
select ORDER_NO ,ITEM ,PART_NO,cast(case when udim1 = '''' then dim1 else dim1/25.4 end * case when udim2 = '''' then dim2 else dim2/25.4 end as numeric(12,2))as t
from tb) b
on a.order_no = b.order_no
left join tc c on a.order_no = c.order_no
group by a.ORDER_NO,a.CUST_NO,b.item,part_no')--Result:
/*ORDER_NO CUST_NO item part_no 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
-------- ------- ---- ------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
A803001 ops 01 220-010 .00 .00 56.20 .00 .00 .00 .00 .00 .00 .00 .00 .00
M803002 WWE 01 33W-001 .00 .00 .00 .00 6393.90 .00 .00 .00 .00 .00 .00 .00
*/
--End
------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-08 17:42:32
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ORDER_NO nvarchar(7),CUST_NO nvarchar(3))
Go
Insert into ta
select 'A803001','ops' union all
select 'M803002','WWE'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(ORDER_NO nvarchar(7),ITEM nvarchar(2),PART_NO nvarchar(7),DIM1 int,UDIM1 nvarchar(2),DIM2 int,UDIM2 nvarchar(2))
Go
Insert into tB
select 'A803001','01','220-010',37,'''''',49,'''''' union all
select 'M803002','01','33W-001',550,'MM',250,'MM'
Go
-- Test Data: tC
If object_id('tC') is not null
Drop table tC
Go
Create table tC(ORDER_NO nvarchar(7),ITEM nvarchar(2),QTY int,Dt smalldatetime)
Go
Insert into tC
select 'A803001','01',20,'2008-03-07' union all
select 'M803002','01',30,'2008-05-30'
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+ltrim(id)+'月]=cast(sum(case when datepart(mm,[dt]) = '+ltrim(id) + ' then qty* b.t else 0 end) as numeric(12,3))'
from (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7
union select 8 union select 9 union select 10 union select 11 union select 12) a
--print @s
exec('select a.*,b.item,part_no ,'+@s+ ',sum(qty * b.t) as 总计 from ta a left join
(
select ORDER_NO ,ITEM ,PART_NO,cast(case when udim1 = '''' then dim1 else dim1/25.4 end * case when udim2 = '''' then dim2 else dim2/25.4 end as numeric(12,2))as t
from tb) b
on a.order_no = b.order_no
left join tc c on a.order_no = c.order_no
group by a.ORDER_NO,a.CUST_NO,b.item,part_no')--Result:
/*
ORDER_NO CUST_NO item part_no 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 总计
-------- ------- ---- ------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- ----------------------------------------
A803001 ops 01 220-010 .000 .000 56.200 .000 .000 .000 .000 .000 .000 .000 .000 .000 56.20
M803002 WWE 01 33W-001 .000 .000 .000 .000 6393.900 .000 .000 .000 .000 .000 .000 .000 6393.90
*/
--End
不要动态EXCEL不支持动态SQL