排程表结构如下:CREATE TABLE [dbo].[LRPLS] (
[LS001] [char] (20) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS002] [char] (20) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS003] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS004] [char] (10) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS005] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS006] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL
) ON [PRIMARY]
GOLS001(计划单ID) LS006(工作中心) LS002(生产线别) LS003(日期) LS004(排程数量) LS005(发放工单否)
0 D 231 20100501 2000 Y
1 D 232 20100501 4000 N
1 D 234 20100503 6000 N
2 D 232 20100502 7000 Y
3 D 235 20100501 1000 N要求最终形成以日期为列名的数据集,但列名是由 ‘起始日期’ 和 ‘截止日期’决定的,比如用户选择是
2010/05/01 到 2010/06/01 则列名要生成这个月的所有日期,并将排程表中的数据显示在对应在日期下,分组以
LS001(计划单ID) LS006(工作中心) LS002(生产线别)为准。盼大侠来指点!
[LS001] [char] (20) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS002] [char] (20) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS003] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS004] [char] (10) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS005] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,
[LS006] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL
) ON [PRIMARY]
GOLS001(计划单ID) LS006(工作中心) LS002(生产线别) LS003(日期) LS004(排程数量) LS005(发放工单否)
0 D 231 20100501 2000 Y
1 D 232 20100501 4000 N
1 D 234 20100503 6000 N
2 D 232 20100502 7000 Y
3 D 235 20100501 1000 N要求最终形成以日期为列名的数据集,但列名是由 ‘起始日期’ 和 ‘截止日期’决定的,比如用户选择是
2010/05/01 到 2010/06/01 则列名要生成这个月的所有日期,并将排程表中的数据显示在对应在日期下,分组以
LS001(计划单ID) LS006(工作中心) LS002(生产线别)为准。盼大侠来指点!
from master..spt_values a left join [LRPLS] b on dateadd(dd,a.number,'2010/05/01') = b.[LS003]where type='P'
and dateadd(dd,number,'2010/05/01')<'2010-06-01'
order by case when b.LS001 is null then char(255) else b.LS001 end ,
case when b.LS006 is null then char(255) else b.LS006 end,
case when b.LS002 is null then char(255) else b.LS002 end/*
日期 LS001 LS006 LS002 LS003 LS004 LS005
----------------------- -------------------- -------- -------------------- -------- ---------- -----
2010-05-01 00:00:00.000 0 D 231 20100501 2000 Y
2010-05-01 00:00:00.000 1 D 232 20100501 4000 N
2010-05-03 00:00:00.000 1 D 234 20100503 6000 N
2010-05-02 00:00:00.000 2 D 232 20100502 7000 Y
2010-05-01 00:00:00.000 3 D 235 20100501 1000 N
2010-05-04 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-05 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-06 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-07 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-08 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-09 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-10 00:00:00.000 NULL NULL NULL NULL NULL NULL
2010-05-11 00:00:00.000 NULL NULL NULL NULL NULL NULL*/
select @sql = isnull(@sql + ',' , '') + QUOTENAME(convert(varchar(10),dateadd(dd,number,'2010/05/01'),120))
from master..spt_values
where type='P' and dateadd(dd,number,'2010/05/01')<'2010-06-01'exec(
'select * from (select ls001,ls006,ls002,ls003,convert(int,ls004) as ls004 from [LRPLS] ) a
pivot(sum(LS004) for [LS003] in ( '+@sql+') ) as b
group by ls001,ls006,ls002 ,'+@sql
)ls001 ls006 ls002 2010-05-01 2010-05-02 2010-05-03 2010-05-04 2010-05-05 2010-05-06 2010-05-07 2010-05-08 2010-05-09 2010-05-10 2010-05-11 2010-05-12 2010-05-13 2010-05-14 2010-05-15 2010-05-16 2010-05-17 2010-05-18 2010-05-19 2010-05-20 2010-05-21 2010-05-22 2010-05-23 2010-05-24 2010-05-25 2010-05-26 2010-05-27 2010-05-28 2010-05-29 2010-05-30 2010-05-31
-------------------- -------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0 D 231 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 D 232 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 D 234 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 D 232 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 D 235 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL(5 行受影响)
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + QUOTENAME(convert(varchar(10),dateadd(dd,number,'2010/05/01'),112))
from master..spt_values
where type='P' and dateadd(dd,number,'2010/05/01')<'2010-06-01'
exec(
'select * from (select ls001,ls006,ls002,ls003,convert(int,ls004) as ls004 from [LRPLS] ) a
pivot(sum(LS004) for [LS003] in ( '+@sql+') ) as b'
)
/*
ls001 ls006 ls002 20100501 20100502 20100503 20100504 20100505 20100506 20100507 20100508 20100509 20100510 20100511 20100512 20100513 20100514 20100515 20100516 20100517 20100518 20100519 20100520 20100521 20100522 20100523 20100524 20100525 20100526 20100527 20100528 20100529 20100530 20100531
-------------------- -------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0 D 231 2000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 D 232 4000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 D 233 NULL NULL 6000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 D 232 NULL 7000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 D 235 1000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL(5 行受影响)*/
select @s=ISNULL(@s,'select ls001,ls006,ls002')
+',max(case rtrim(LS003) when '''+LS003+''' then LS004 else 0 end)['+LS003+']'
from (
select LS003=convert(varchar(10),dateadd(dd,number,'2010/05/01'),112)
from master..spt_values
where type='p'
and number<datediff(dd,'2010-05-01','2010-06-01')
) as t
exec(@s+' from LRPLS group by ls001,ls006,ls002')
select @s=ISNULL(@s,'select ls001,ls006,ls002')
+',max(case rtrim(LS003) when '''+LS003+''' then LS004 else 0 end)['+LS003+']'
from (
select LS003=convert(varchar(10),dateadd(dd,number,'2010/05/01'),112)
from master..spt_values
where type='p'
and number<datediff(dd,'2010-05-01','2010-06-01')
) as t
exec(@s+' from LRPLS group by ls001,ls006,ls002')
--2000的,居然没超过varchar8000
declare @sql varchar(8000)
set @sql = 'select ls001,ls006,ls002 '
select @sql = @sql + ' , max(case LS003 when ''' + rq + ''' then LS004 else 0 end) [' + rq + ']'
from (select convert(varchar(10),dateadd(dd,number,'2010/05/01'),112) AS rq
from master..spt_values
where type='p' AND dateadd(dd,number,'2010/05/01')<'2010-06-01') as a
set @sql = @sql + ' from [LRPLS] group by ls001,ls006,ls002'
exec(@sql) /*ls001 ls006 ls002 20100501 20100502 20100503 20100504 20100505 20100506 20100507 20100508 20100509 20100510 20100511 20100512 20100513 20100514 20100515 20100516 20100517 20100518 20100519 20100520 20100521 20100522 20100523 20100524 20100525 20100526 20100527 20100528 20100529 20100530 20100531
-------------------- -------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0 D 231 2000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 D 232 4000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 D 233 0 0 6000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2 D 232 0 7000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3 D 235 1000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0(5 行受影响)*/
在我这里是没有记录的:) sql2K sp4 二进制