我有两张表表一DATE(日历表)
DATE
2009-12-01
2009-12-02
2009-12-03
2009-12-04
2009-12-05
2009-12-06
2009-12-07
2009-12-08
.....(日期自动延伸)表二SO(销售订单明细表)
SO CU_name QTY start_date
SO12001 大客户 1000 2009-12-01
SO12002 小客户 200 2009-12-01
SO12003 小客户 500 2009-12-02
SO12004 中客户 1000 2009-12-04
SO12005 大客户 5000 2009-12-05
.....随时增加该表内容注:两张表没有关联关系,只有从时间上可以看到有联系。
想得到的结果
第一行是日期如: 2009-12-01 2009-12-02 2009-12-03 2009-12-04 2009-12-05 2009-12-06 2009-12-07
一列为SO 二列为客户
SO CU_name
SO12001 大客户 1000
SO12002 小客户 200
SO12003 小客户 500
SO12004 中客户 1000
SO12005 大客户 5000我的意思:1、将日历由纵向改为横向,2、将销售订单明细中的数量,以开工日期为依据自动填写到日期“横向”列的交叉处。请各位高手多多指点!!!
DATE
2009-12-01
2009-12-02
2009-12-03
2009-12-04
2009-12-05
2009-12-06
2009-12-07
2009-12-08
.....(日期自动延伸)表二SO(销售订单明细表)
SO CU_name QTY start_date
SO12001 大客户 1000 2009-12-01
SO12002 小客户 200 2009-12-01
SO12003 小客户 500 2009-12-02
SO12004 中客户 1000 2009-12-04
SO12005 大客户 5000 2009-12-05
.....随时增加该表内容注:两张表没有关联关系,只有从时间上可以看到有联系。
想得到的结果
第一行是日期如: 2009-12-01 2009-12-02 2009-12-03 2009-12-04 2009-12-05 2009-12-06 2009-12-07
一列为SO 二列为客户
SO CU_name
SO12001 大客户 1000
SO12002 小客户 200
SO12003 小客户 500
SO12004 中客户 1000
SO12005 大客户 5000我的意思:1、将日历由纵向改为横向,2、将销售订单明细中的数量,以开工日期为依据自动填写到日期“横向”列的交叉处。请各位高手多多指点!!!
*
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-03-09
地点:广东深圳
说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
当然如果你必须写在数据库中的话,就参考一楼了
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-08 10:50:06
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[DATE]
if object_id('[DATE]') is not null drop table [DATE]
go
create table [DATE]([DATE] datetime)
insert [DATE]
select '2009-12-01' union all
select '2009-12-02' union all
select '2009-12-03' union all
select '2009-12-04' union all
select '2009-12-05' union all
select '2009-12-06' union all
select '2009-12-07' union all
select '2009-12-08'
--> 测试数据:[SO]
if object_id('[SO]') is not null drop table [SO]
go
create table [SO]([SO] varchar(7),[CU_name] varchar(6),[QTY] int,[start_date] datetime)
insert [SO]
select 'SO12001','大客户',1000,'2009-12-01' union all
select 'SO12002','小客户',200,'2009-12-01' union all
select 'SO12003','小客户',500,'2009-12-02' union all
select 'SO12004','中客户',1000,'2009-12-04' union all
select 'SO12005','大客户',5000,'2009-12-05'
--------------开始查询--------------------------
select
b.so,b.CU_name,
max(case [start_date] when '2009-12-01' then QTY else '' end) as '2009-12-01',
max(case [start_date] when '2009-12-02' then QTY else '' end) as '2009-12-02',
max(case [start_date] when '2009-12-03' then QTY else '' end) as '2009-12-03',
max(case [start_date] when '2009-12-04' then QTY else '' end) as '2009-12-04',
max(case [start_date] when '2009-12-05' then QTY else '' end) as '2009-12-05'
from
[DATE] a
left join
so b
on
a.DATE=b.start_date
group by
b.so,b.CU_name
----------------结果----------------------------
/* so CU_name 2009-12-01 2009-12-02 2009-12-03 2009-12-04 2009-12-05
------- ------- ----------- ----------- ----------- ----------- -----------
NULL NULL 0 0 0 0 0
SO12001 大客户 1000 0 0 0 0
SO12005 大客户 0 0 0 0 5000
SO12002 小客户 200 0 0 0 0
SO12003 小客户 0 500 0 0 0
SO12004 中客户 0 0 0 1000 0(6 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-08 10:50:06
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[DATE]
if object_id('[DATE]') is not null drop table [DATE]
go
create table [DATE]([DATE] datetime)
insert [DATE]
select '2009-12-01' union all
select '2009-12-02' union all
select '2009-12-03' union all
select '2009-12-04' union all
select '2009-12-05' union all
select '2009-12-06' union all
select '2009-12-07' union all
select '2009-12-08'
--> 测试数据:[SO]
if object_id('[SO]') is not null drop table [SO]
go
create table [SO]([SO] varchar(7),[CU_name] varchar(6),[QTY] int,[start_date] datetime)
insert [SO]
select 'SO12001','大客户',1000,'2009-12-01' union all
select 'SO12002','小客户',200,'2009-12-01' union all
select 'SO12003','小客户',500,'2009-12-02' union all
select 'SO12004','中客户',1000,'2009-12-04' union all
select 'SO12005','大客户',5000,'2009-12-05'
--------------开始查询--------------------------
select
b.so,b.CU_name,
max(case [start_date] when '2009-12-01' then QTY else '' end) as '2009-12-01',
max(case [start_date] when '2009-12-02' then QTY else '' end) as '2009-12-02',
max(case [start_date] when '2009-12-03' then QTY else '' end) as '2009-12-03',
max(case [start_date] when '2009-12-04' then QTY else '' end) as '2009-12-04',
max(case [start_date] when '2009-12-05' then QTY else '' end) as '2009-12-05'
from
[DATE] a
join
so b
on
a.DATE=b.start_date
group by
b.so,b.CU_name
----------------结果----------------------------
/*so CU_name 2009-12-01 2009-12-02 2009-12-03 2009-12-04 2009-12-05
------- ------- ----------- ----------- ----------- ----------- -----------
SO12001 大客户 1000 0 0 0 0
SO12002 小客户 200 0 0 0 0
SO12003 小客户 0 500 0 0 0
SO12004 中客户 0 0 0 1000 0
SO12005 大客户 0 0 0 0 5000(5 行受影响)
*/
insert into DATE values('2009-12-01')
insert into DATE values('2009-12-02')
insert into DATE values('2009-12-03')
insert into DATE values('2009-12-04')
insert into DATE values('2009-12-05')
insert into DATE values('2009-12-06')
insert into DATE values('2009-12-07')
insert into DATE values('2009-12-08')
create table SO(SO varchar(10), CU_name varchar(10), QTY int, start_date datetime)
insert into SO values('SO12001' , '大客户' , 1000, '2009-12-01')
insert into SO values('SO12002' , '小客户' , 200 , '2009-12-01')
insert into SO values('SO12003' , '小客户' , 500 , '2009-12-02')
insert into SO values('SO12004' , '中客户' , 1000, '2009-12-04')
insert into SO values('SO12005' , '大客户' , 5000, '2009-12-05')
go
declare @sql varchar(8000)
set @sql = 'select m.SO , m.CU_name '
select @sql = @sql + ' , sum(case convert(varchar(10),m.start_date,120) when ''' + date + ''' then QTY else 0 end) [' + date + ']'
from (select distinct convert(varchar(10),DATE,120) date from date) as a
set @sql = @sql + ' from SO m ,DATE n where convert(varchar(10),m.start_date,120) = convert(varchar(10),n.DATE,120) group by m.SO , m.CU_name order by m.SO , m.CU_name'
exec(@sql) drop table DATE , so/*
SO CU_name 2009-12-01 2009-12-02 2009-12-03 2009-12-04 2009-12-05 2009-12-06 2009-12-07 2009-12-08
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
SO12001 大客户 1000 0 0 0 0 0 0 0
SO12002 小客户 200 0 0 0 0 0 0 0
SO12003 小客户 0 500 0 0 0 0 0 0
SO12004 中客户 0 0 0 1000 0 0 0 0
SO12005 大客户 0 0 0 0 5000 0 0 0*/