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

wangxiaofeiwuqiao 2010-09-22 03:02:47
有如下表：


(物料编码)       （物料名称）   (需求数量)      (需求日期)

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-22


ITEM_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
...全文
163 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
SQLCenter 2010-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 wangxiaofeiwuqiao 的回复:]

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

这个也搞定了，LEFT JOIN 就出来了，见你的原贴。
wangxiaofeiwuqiao 2010-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 sqlcenter 的回复:]
看漏了一个条件：本周以前的所有需求总计作为0wk
改改


SQL code
--> 测试数据：#
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……
[/Quote]
谢谢。是的，本周以前的所有需求总计作为0wk，其他的数据是按照每周的需求总和计算。
SQLCenter 2010-09-22
  • 打赏
  • 举报
 回复
看漏了一个条件：本周以前的所有需求总计作为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

*/
wangxiaofeiwuqiao 2010-09-22
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 hao1hao2hao3 的回复:]
中秋快乐，还是你！昨天那家伙没帮你搞定吗？
[/Quote]
没有啊，要不你帮帮忙嘛，
wangxiaofeiwuqiao 2010-09-22
  • 打赏
  • 举报
 回复
谢谢，那位高手顺便再帮解决下这个，跟上面的很类似的http://topic.csdn.net/u/20100921/14/b3db2cdf-eec2-435c-8658-da831c8760a8.html
SQLCenter 2010-09-22
  • 打赏
  • 举报
 回复 
--> 测试数据：#

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

*/
Mark杨 2010-09-22
  • 打赏
  • 举报
 回复 


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 行）





和你数据一样了 呵呵

*/
Mark杨 2010-09-22
  • 打赏
  • 举报
 回复
我 ‘塑胶A’ 的第3条数据 和你的日期不一样 所以结果不一样
Mark杨 2010-09-22
  • 打赏
  • 举报
 回复 


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 行）

*/
hao1hao2hao3 2010-09-22
  • 打赏
  • 举报
 回复
中秋快乐，还是你！昨天那家伙没帮你搞定吗？
wangxiaofeiwuqiao 2010-09-22
  • 打赏
  • 举报
 回复
祝大家中秋快乐！
java 动态行转列_行转列的三种实现方式
碰到了需要将取出的数据进行行转列的操作，然后显示出来的问题，当时是吧这个问题交给了前端小姐姐，麻烦她来处理了，但是后来抽空自己研究了一下，发现其实有三种实现方式，下面直接上代码一一说明，以供参考(一)、...
数据库行转列的sql语句（转）
数据库行转列的sql语句问题描述假设有张学生成绩表(CJ)如下Name Subject Result张三 语文 80张三 数学 90张三 物理 85李四 语文 85李四 数学 92李四 物理 82现在 想写 sql 语句 查询后结果 为 姓名 语文 数学 物理...
行转列的三种实现方式
上次项目中，碰到了需要将取出的数据进行行转列的操作，然后显示出来的问题，当时是吧这个问题交给了前端小姐姐，麻烦她来处理了，但是后来抽空自己研究了一下，发现其实有三种实现方式，下面直接上代码一一说明，以...
mysql中将列动态转换为行,mysql 行转列 MySQL数据库动态行转列
想把mysql一个表的行转成列，图1是原表，想实现图2的样式SELECT MAX(CAS就是一个动态的行列转换 CREATE TABLE `c_wssb_zz` ( `aa011` varchar(10) default NULL, `aa001` varchar(20) default NULL, `aa014` varchar...
经典Sql面试题收集！！
经典Sql面试题收集！！ 1.用一条SQL语句 查询出每门课都大于80分的学生姓名  name kecheng fenshu  张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 ...
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章