上面催的急，无奈再次发帖，修改存储过程

CREATE TABLE [VIEW_DS_MOTime]([ITEM_CODE] varchar(20) , [DES]  nvarchar(10)   ,    [TO_BE_FLLW]  int ,    [DATE_START]  DATETIME   ,    [SO_NUMBER]  VARCHAR(20) null ,[HDREMARK] VARCHAR(20) null)

--插入测试数据

INSERT INTO [VIEW_DS_MOTime] ([ITEM_CODE],[DES],[TO_BE_FLLW],[DATE_START],[SO_NUMBER],[HDREMARK])

    SELECT '21-00001-00010',N'塑胶A',60,'2010-09-07','SO1012440',null UNION

    SELECT '21-00001-00010',N'塑胶A',40,'2010-09-28','SO1012441',null UNION

    SELECT '21-00001-00010',N'塑胶A',10,'2010-11-05','SO1012472',null UNION

    SELECT '21-00002-00030',N'塑胶C',50,'2010-09-11','SO1012458',null UNION

    SELECT '21-00002-00030',N'塑胶C',80,'2010-11-11',null,'#98-01015' UNION

    SELECT '21-00002-00030',N'塑胶C',30,'2010-11-12','SO1012458',null UNION

    SELECT '21-00002-00030',N'塑胶C',40,'2010-11-17','SO1012458',null

CREATE TABLE [SO_LN1](    [INVOICE]  VARCHAR(20)   ,    [CODE]  VARCHAR(20)  )

--插入测试数据

INSERT INTO [SO_LN1] ([INVOICE],[CODE])

    SELECT 'SO1012440','#98-01012' UNION

    SELECT 'SO1012440','#98-01013' UNION

    SELECT 'SO1012441','#98-01018' UNION

    SELECT 'SO1012472','#98-01014' UNION

    SELECT 'SO1012458','#98-01015' UNION

    SELECT 'SO1012458','#98-01016' UNION

    SELECT 'SO1012458','#98-01017'

GO









create function dbo.f_str(@ITEM_CODE varchar(20),@des varchar(20) ,@date_num int, @dt datetime) returns varchar(1000)

as

begin

  declare @str varchar(1000)

  select @str = isnull(@str + ',' , '') + cast(code as varchar) from 

  (

    select distinct t1.* , t2.CODE from

    (

      SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end

    ) t1,

	(

		select m.[ITEM_CODE],m.[DES],m.[TO_BE_FLLW],m.[DATE_START],n.[CODE] from [VIEW_DS_MOTime] m,[SO_LN1] n where (m.[SO_NUMBER] = n.[INVOICE] or m.[HDREMARK] = n.[CODE])

	) t2

    where t1.ITEM_CODE = t2.ITEM_CODE

    and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff(week,@dt,t2.date_start) + 1 end) = t1.date_num

  ) o

  where ITEM_CODE = @ITEM_CODE and des = @des and date_num = @date_num

  return @str

end

go





declare @dt as datetime

set @dt = '2010-11-06'



select ITEM_CODE,des,

       max(case date_num when 0 then ltrim(to_be_fllw) else '' end) [0wk],

       max(case date_num when 0 then code else '' end) [0wk需求成品],

       max(case date_num when 1 then ltrim(to_be_fllw) else '' end) [1wk],

       max(case date_num when 1 then code else '' end) [1wk需求成品],

       max(case date_num when 2 then ltrim(to_be_fllw) else '' end) [2wk],

       max(case date_num when 2 then code else '' end) [2wk需求成品],

       max(case date_num when 3 then ltrim(to_be_fllw) else '' end) [3wk],

       max(case date_num when 3 then code else '' end) [3wk需求成品]

from

(

select ITEM_CODE , des , date_num , max(to_be_fllw) to_be_fllw, code = dbo.f_str(ITEM_CODE , des , date_num,@dt) from 

(

select distinct t1.* , t2.CODE from

(

SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end

) t1,

(

	select m.[ITEM_CODE],m.[DES],m.[TO_BE_FLLW],m.[DATE_START],n.[CODE] from [VIEW_DS_MOTime] m,[SO_LN1] n where (m.[SO_NUMBER] = n.[INVOICE] or m.[HDREMARK] = n.[CODE])

) t2

where t1.ITEM_CODE = t2.ITEM_CODE

and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff(week,@dt,t2.date_start) + 1 end) = t1.date_num

) o

group by ITEM_CODE , des , date_num

) k

group by ITEM_CODE,des