3个很简单的表合成一个，并且行转列，难，高手Come in!

--> 测试数据：#st

if object_id('tempdb.dbo.#st') is not null drop table #st

create table #st(ITEM_CODE varchar(14), DES varchar(8), SNumber int, ON_HAND varchar(8))

insert into #st

select '21-00001-00010', '塑胶A', 500, 'W' union all

select '21-00001-00010', '塑胶A', 400, 'Y' union all

select '21-00002-00020', '塑胶B', 100, 'W' union all

select '21-00002-00030', '塑胶C', 200, 'W' union all

select '21-00002-00030', '塑胶C', 600, 'Y'

--> 测试数据：#po

if object_id('tempdb.dbo.#po') is not null drop table #po

create table #po(ITEM_CODE varchar(14), DES varchar(8), PONumber int)

insert into #po

select '21-00001-00010', '塑胶A', 8000 union all

select '21-00002-00020', '塑胶B', 300

--> 测试数据：#mo

if object_id('tempdb.dbo.#mo') is not null drop table #mo

create table #mo(ITEM_CODE varchar(14), DES varchar(8), TO_BE_FLLW int, DATE_START datetime)

insert into #mo

select '21-00001-00010', '塑胶A', 60, '2010-09-07' union all

select '21-00001-00010', '塑胶A', 40, '2010-09-28' union all

select '21-00001-00010', '塑胶A', 10, '2010-10-14' union all

select '21-00002-00030', '塑胶C', 50, '2010-09-27' union all

select '21-00002-00030', '塑胶C', 80, '2010-09-28' union all

select '21-00002-00030', '塑胶C', 40, '2010-11-29'



-- 本周星期日0点

declare @ datetime

set @ = convert(varchar, (getdate() - (@@datefirst - 1 + datepart(weekday, getdate())) % 7), 112)



select a.*, PO=isnull(b.PONumber,0), [0wk]=isnull([0wk],0),

    [1wk]=isnull([1wk],0), [2wk]=isnull([2wk],0), [3wk]=isnull([3wk],0),

    [4wk]=isnull([4wk],0), [5wk]=isnull([5wk],0), [6wk]=isnull([6wk],0),

    [7wk]=isnull([7wk],0), [8wk]=isnull([8wk],0), [9wk]=isnull([9wk],0),

    [10wk]=isnull([10wk],0), [11wk]=isnull([11wk],0), [12wk]=isnull([12wk],0)

from

(

    select ITEM_CODE, DES,

        WIP = sum(case ON_HAND when 'W' then SNumber else 0 end),

        STOCK = sum(case ON_HAND when 'Y' then SNumber else 0 end)

    from #st group by ITEM_CODE, DES

) a

left join

    #po b on a.ITEM_CODE = b.ITEM_CODE

left join

(

    select ITEM_CODE,DES,

        sum(case when DATE_START < @ then TO_BE_FLLW else 0 end) '0wk',

        sum(case datediff(day,@,DATE_START)/7 when 0 then TO_BE_FLLW else 0 end) '1wk',

        sum(case datediff(day,@,DATE_START)/7 when 1 then TO_BE_FLLW else 0 end) '2wk',

        sum(case datediff(day,@,DATE_START)/7 when 2 then TO_BE_FLLW else 0 end) '3wk',

        sum(case datediff(day,@,DATE_START)/7 when 3 then TO_BE_FLLW else 0 end) '4wk',

        sum(case datediff(day,@,DATE_START)/7 when 4 then TO_BE_FLLW else 0 end) '5wk',

        sum(case datediff(day,@,DATE_START)/7 when 5 then TO_BE_FLLW else 0 end) '6wk',

        sum(case datediff(day,@,DATE_START)/7 when 6 then TO_BE_FLLW else 0 end) '7wk',

        sum(case datediff(day,@,DATE_START)/7 when 7 then TO_BE_FLLW else 0 end) '8wk',

        sum(case datediff(day,@,DATE_START)/7 when 8 then TO_BE_FLLW else 0 end) '9wk',

        sum(case datediff(day,@,DATE_START)/7 when 9 then TO_BE_FLLW else 0 end) '10wk',

        sum(case datediff(day,@,DATE_START)/7 when 10 then TO_BE_FLLW else 0 end) '11wk',

        sum(case datediff(day,@,DATE_START)/7 when 11 then TO_BE_FLLW else 0 end) '12wk'

    from #mo group by ITEM_CODE,DES

) c

on a.ITEM_CODE = c.ITEM_CODE order by 1

/*

ITEM_CODE      DES      WIP         STOCK       PO          0wk         1wk         2wk         3wk         4wk         5wk         6wk         7wk         8wk         9wk         10wk        11wk        12wk

-------------- -------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

21-00001-00010 塑胶A    500         400         8000        60          0           40          0           10          0           0           0           0           0           0           0           0

21-00002-00020 塑胶B    100         0           300         0           0           0           0           0           0           0           0           0           0           0           0           0

21-00002-00030 塑胶C    200         600         0           0           0           130         0           0           0           0           0           0           0           0           40          0

*/