不算复杂的sql 行转列！高手进。

Question

有如下表：(物料编码)       （物料名称）   (需求数量)      (需求日期)
ITEM_CODE           DES        TO_BE_FLLW        DATE_START
21-00001-00010      塑胶A        60              2010-09-07
21-00001-00010      塑胶A        40              2010-09-28
21-00001-00010      塑胶A        10              2010-10-14
21-00002-00030      塑胶C        50              2010-09-27
21-00002-00030      塑胶C        80              2010-09-28
21-00002-00030      塑胶C        40              2010-11-29
我想把他转换成如下效果：
统计表未来12周的需求数量，以周为统计单位，当周内所有的需求总数为一个数据。
获取当日所在周为1wk，本周以前的所有需求总计作为0wk,下周为2wk,依次类推每周从星期日开始(星期日没有记录，实际从星期一开始)，星期六结束。假如今天是2010-09-22ITEM_CODE        DES         0wk    1wk     2wk    3wk     4wk   ......  11wk    12wk
21-00001-00010  塑胶A         60     0      40      0      10    ......   0       0
21-00002-00030  塑胶C         0      0      130     0       0    ......   40      0
举例：塑胶C 在2wk的130是2010-09-27日的50加上2010-09-28的 80而得如果理论上不能统计0wk的数据那就不统计了，只统计1wk-12wk的数据。
数据库环境：sql server 2000

Answer 1

declare @ta table (ITEM_CODE nvarchar(20),DES nvarchar(10),TO_BE_FLLW int,DATE_START datetime)insert @ta 
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-09-14' union all
select '21-00001-00030','塑胶C',50,'2010-09-27' union all
select '21-00001-00030','塑胶C',80,'2010-09-28' union all
select '21-00001-00030','塑胶C',40,'2010-11-29'
select ITEM_CODE,DES,
sum(case when datename(week,DATE_START) < datename(week,getdate()) then TO_BE_FLLW else 0 end) '0wk',
sum(case when datename(week,DATE_START) = datename(week,getdate()) then TO_BE_FLLW else 0 end) '1wk',
sum(case when datename(week,DATE_START)-1 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '2wk',
sum(case when datename(week,DATE_START)-2 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '3wk',
sum(case when datename(week,DATE_START)-3 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '4wk',
sum(case when datename(week,DATE_START)-4 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '5wk',
sum(case when datename(week,DATE_START)-5 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '6wk',
sum(case when datename(week,DATE_START)-6 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '7wk',
sum(case when datename(week,DATE_START)-7 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '8wk',
sum(case when datename(week,DATE_START)-8 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '9wk',
sum(case when datename(week,DATE_START)-9 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '10wk',
sum(case when datename(week,DATE_START)-10 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '11wk',
sum(case when datename(week,DATE_START)-11 = datename(week,getdate()) then TO_BE_FLLW else 0 end) '12wk'
from @ta group by ITEM_CODE,DES/*（所影响的行数为 6 行）ITEM_CODE            DES        0wk         1wk         2wk         3wk         4wk         5wk         6wk         7wk         8wk         9wk         10wk        11wk        12wk        
-------------------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
21-00001-00010       塑胶A        70          0           40          0           0           0           0           0           0           0           0           0           0
21-00001-00030       塑胶C        0           0           130         0           0           0           0           0           0           0           0           40          0（所影响的行数为 2 行）
*/

Answer 2

declare @ta table (ITEM_CODE nvarchar(20),DES nvarchar(10),TO_BE_FLLW int,DATE_START datetime)insert @ta 
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-00001-00030','塑胶C',50,'2010-09-27' union all
select '21-00001-00030','塑胶C',80,'2010-09-28' union all
select '21-00001-00030','塑胶C',40,'2010-11-29'
declare @t datetime
set @t = '2010-9-22'  --假设今天为 2010-9-22 select ITEM_CODE,DES,
sum(case when datename(week,DATE_START) < datename(week,@t) then TO_BE_FLLW else 0 end) '0wk',
sum(case when datename(week,DATE_START) = datename(week,@t) then TO_BE_FLLW else 0 end) '1wk',
sum(case when datename(week,DATE_START)-1 = datename(week,@t) then TO_BE_FLLW else 0 end) '2wk',
sum(case when datename(week,DATE_START)-2 = datename(week,@t) then TO_BE_FLLW else 0 end) '3wk',
sum(case when datename(week,DATE_START)-3 = datename(week,@t) then TO_BE_FLLW else 0 end) '4wk',
sum(case when datename(week,DATE_START)-4 = datename(week,@t) then TO_BE_FLLW else 0 end) '5wk',
sum(case when datename(week,DATE_START)-5 = datename(week,@t) then TO_BE_FLLW else 0 end) '6wk',
sum(case when datename(week,DATE_START)-6 = datename(week,@t) then TO_BE_FLLW else 0 end) '7wk',
sum(case when datename(week,DATE_START)-7 = datename(week,@t) then TO_BE_FLLW else 0 end) '8wk',
sum(case when datename(week,DATE_START)-8 = datename(week,@t) then TO_BE_FLLW else 0 end) '9wk',
sum(case when datename(week,DATE_START)-9 = datename(week,@t) then TO_BE_FLLW else 0 end) '10wk',
sum(case when datename(week,DATE_START)-10 = datename(week,@t) then TO_BE_FLLW else 0 end) '11wk',
sum(case when datename(week,DATE_START)-11 = datename(week,@t) then TO_BE_FLLW else 0 end) '12wk'
from @ta group by ITEM_CODE,DES/*（所影响的行数为 6 行）ITEM_CODE            DES        0wk         1wk         2wk         3wk         4wk         5wk         6wk         7wk         8wk         9wk         10wk        11wk        12wk        
-------------------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
21-00001-00010       塑胶A        60          0           40          0           10          0           0           0           0           0           0           0           0
21-00001-00030       塑胶C        0           0           130         0           0           0           0           0           0           0           0           40          0（所影响的行数为 2 行）
和你数据一样了 呵呵
*/

Answer 3

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ITEM_CODE varchar(14), DES varchar(8), TO_BE_FLLW int, DATE_START datetime)
insert into #
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'declare @ datetime
-- 0周星期日的日期
set @ = getdate() - (@@datefirst - 1 + datepart(weekday, getdate())) % 7 - 7-- 支持跨年
select ITEM_CODE,DES,
    sum(case datediff(day,@,DATE_START)/7 when 0 then TO_BE_FLLW else 0 end) '0wk',
    sum(case datediff(day,@,DATE_START)/7 when 1 then TO_BE_FLLW else 0 end) '1wk',
    sum(case datediff(day,@,DATE_START)/7 when 2 then TO_BE_FLLW else 0 end) '2wk',
    sum(case datediff(day,@,DATE_START)/7 when 3 then TO_BE_FLLW else 0 end) '3wk',
    sum(case datediff(day,@,DATE_START)/7 when 4 then TO_BE_FLLW else 0 end) '4wk',
    sum(case datediff(day,@,DATE_START)/7 when 5 then TO_BE_FLLW else 0 end) '5wk',
    sum(case datediff(day,@,DATE_START)/7 when 6 then TO_BE_FLLW else 0 end) '6wk',
    sum(case datediff(day,@,DATE_START)/7 when 7 then TO_BE_FLLW else 0 end) '7wk',
    sum(case datediff(day,@,DATE_START)/7 when 8 then TO_BE_FLLW else 0 end) '8wk',
    sum(case datediff(day,@,DATE_START)/7 when 9 then TO_BE_FLLW else 0 end) '9wk',
    sum(case datediff(day,@,DATE_START)/7 when 10 then TO_BE_FLLW else 0 end) '10wk',
    sum(case datediff(day,@,DATE_START)/7 when 11 then TO_BE_FLLW else 0 end) '11wk',
    sum(case datediff(day,@,DATE_START)/7 when 12 then TO_BE_FLLW else 0 end) '12wk'
from # group by ITEM_CODE,DES/*
ITEM_CODE      DES      0wk         1wk         2wk         3wk         4wk         5wk         6wk         7wk         8wk         9wk         10wk        11wk        12wk
-------------- -------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
21-00001-00010 塑胶A      60          0           40          0           10          0           0           0           0           0           0           0           0
21-00002-00030 塑胶C      0           0           130         0           0           0           0           0           0           0           0           40          0
*/

Answer 4

谢谢，那位高手顺便再帮解决下这个，跟上面的很类似的http://topic.csdn.net/u/20100921/14/b3db2cdf-eec2-435c-8658-da831c8760a8.html

Answer 5

看漏了一个条件：本周以前的所有需求总计作为0wk
改改--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ITEM_CODE varchar(14), DES varchar(8), TO_BE_FLLW int, DATE_START datetime)
insert into #
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'declare @ datetime
-- 本周星期日0点
set @ = convert(varchar, (getdate() - (@@datefirst - 1 + datepart(weekday, getdate())) % 7), 112)-- 支持跨年
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 # group by ITEM_CODE,DES/*
ITEM_CODE      DES      0wk         1wk         2wk         3wk         4wk         5wk         6wk         7wk         8wk         9wk         10wk        11wk        12wk
-------------- -------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
21-00001-00010 塑胶A      60          0           40          0           10          0           0           0           0           0           0           0           0
21-00002-00030 塑胶C      0           0           130         0           0           0           0           0           0           0           0           40          0
*/

Answer 6

这个也搞定了，LEFT JOIN 就出来了，见你的原贴。