declare @sql varchar(8000)
set @sql='select 拉号,产品编号,产品名称'
select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']'
from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb) tt) texec (@sql+' from tb group by 拉号,产品编号,产品名称')
set @sql='select 拉号,产品编号,产品名称'
select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']'
from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb) tt) texec (@sql+' from tb group by 拉号,产品编号,产品名称')
declare @sql varchar(8000)
set @sql='select 拉号,产品编号,产品名称'
select @sql=@sql+',['+convert(varchar(10),日期,120)+']=sum(case 日期 when '''+convert(varchar(10),日期,120)+''' then 日生产数量 else 0 end)'
from (select distinct convert(varchar(10),日期,120) from 表名)a
set @sql=@sql+' from 表名 group by 拉号,产品编号,产品名称'
exec(@sql)
insert into tb select '01' , 'k1084' , 'A公仔' , 1000 , '2008/05/10'
insert into tb select '01' , 'k1088' , 'B公仔' , 500 , '2008/05/11'
insert into tb select '01' , 'k1085' , 'C公仔' , 5000 , '2008/05/12'
insert into tb select '01' , 'k1085' , 'D公仔' , 10000 , '2008/05/13'
insert into tb select '02' , 'k1087' , 'E公仔' , 6000 , '2008/05/10'
insert into tb select '02' , 'k1088' , 'B公仔' , 3000 , '2008/05/11'
insert into tb select '02' , 'k1089' , 'G公仔' , 9000 ,'2008/05/12'
insert into tb select '02' , 'k1084' , 'A公仔' , 6000 ,'2008/05/13'
godeclare @sql varchar(8000)
set @sql='select 拉号,产品编号,产品名称'
select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']'
from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb) tt) texec (@sql+' from tb group by 拉号,产品编号,产品名称')go
drop table tb/*
拉号 产品编号 产品名称 05/10/2008 05/11/2008 05/12/2008 05/13/2008
----- ---------- ---------- ----------- ----------- ----------- -----------
01 k1084 A公仔 1000 0 0 0
01 k1085 C公仔 0 0 5000 0
01 k1085 D公仔 0 0 0 10000
01 k1088 B公仔 0 500 0 0
02 k1084 A公仔 0 0 0 6000
02 k1087 E公仔 6000 0 0 0
02 k1088 B公仔 0 3000 0 0
02 k1089 G公仔 0 0 9000 0
*/
insert into tb select '01' , 'k1084' , 'A公仔' , 1000 , '2008/05/10'
insert into tb select '01' , 'k1088' , 'B公仔' , 500 , '2008/05/11'
insert into tb select '01' , 'k1085' , 'C公仔' , 5000 , '2008/05/12'
insert into tb select '01' , 'k1085' , 'D公仔' , 10000 , '2008/05/13'
insert into tb select '02' , 'k1087' , 'E公仔' , 6000 , '2008/05/10'
insert into tb select '02' , 'k1088' , 'B公仔' , 3000 , '2008/05/11'
insert into tb select '02' , 'k1089' , 'G公仔' , 9000 ,'2008/05/12'
insert into tb select '02' , 'k1084' , 'A公仔' , 6000 ,'2008/05/13'
godeclare @sql varchar(8000)
set @sql='select 拉号,产品编号,产品名称'
select @sql=@sql+',sum(case when convert(varchar(10),日期,101)='''+日期+''' then 日生产数量 else 0 end) ['+日期+']'
from (select distinct 日期 from( select top 100 percent convert(varchar(10),日期,101) as 日期 from tb order by 日期) tt) texec (@sql+' from tb group by 拉号,产品编号,产品名称')go
drop table tb/*
拉号 产品编号 产品名称 05/10/2008 05/11/2008 05/12/2008 05/13/2008
----- ---------- ---------- ----------- ----------- ----------- -----------
01 k1084 A公仔 1000 0 0 0
01 k1085 C公仔 0 0 5000 0
01 k1085 D公仔 0 0 0 10000
01 k1088 B公仔 0 500 0 0
02 k1084 A公仔 0 0 0 6000
02 k1087 E公仔 6000 0 0 0
02 k1088 B公仔 0 3000 0 0
02 k1089 G公仔 0 0 9000 0
*/
set @exec='select 拉号,产口编号,产品名称'
select @exec=@exec+',(case when 日期 ='''+ 日期 +''' then 日生产数量 else 0 end) as '''+日期+'''' from tablename group by 日期
set @exec=@exec+'from tablename order by 拉号'
exec(@exec)
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(拉号 varchar(2),产口编号 varchar(5),产品名称 varchar(6),日生产数量 int,日期 smalldatetime)
Go
Insert into Ta
select '01','k1084','A公仔',1000,'2008/05/10' union all
select '01','k1088','B公仔',500,'2008/05/11' union all
select '01','k1085','C公仔',5000,'2008/05/12' union all
select '01','k1085','D公仔',10000,'2008/05/13' union all
select '02','k1087','E公仔',6000,'2008/05/10' union all
select '02','k1088','B公仔',3000,'2008/05/11' union all
select '02','k1089','G公仔',9000,'2008/05/12' union all
select '02','k1084','A公仔',6000,'2008/05/13'
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','')+'['+convert(char(10),日期,120)+'] = max(case when datediff(d,日期,'''+ convert(char(10),日期,120)+''') = 0 then 日生产数量 else 0 end)'
from (select distinct 日期 from ta) a
exec('select 拉号,产口编号,产品名称,'+ @s + ' from ta group by 拉号,产口编号,产品名称
order by 拉号,产品名称')--Result:
/*
拉号 产口编号 产品名称 2008-05-10 2008-05-11 2008-05-12 2008-05-13
---- ----- ------ ----------- ----------- ----------- -----------
01 k1084 A公仔 1000 0 0 0
01 k1085 C公仔 0 0 5000 0
01 k1085 D公仔 0 0 0 10000
01 k1088 B公仔 0 500 0 0
02 k1084 A公仔 0 0 0 6000
02 k1087 E公仔 6000 0 0 0
02 k1088 B公仔 0 3000 0 0
02 k1089 G公仔 0 0 9000 0*/
--End
create table tb(拉号 varchar(5),产品编号 varchar(10),产品名称 varchar(10),日生产数量 int,日期 datetime)
insert into tb select '01' , 'k1084' , 'A公仔' , 1000 , '2008/05/10'
insert into tb select '01' , 'k1088' , 'B公仔' , 500 , '2008/05/11'
insert into tb select '01' , 'k1085' , 'C公仔' , 5000 , '2008/05/12'
insert into tb select '01' , 'k1085' , 'D公仔' , 10000 , '2008/05/13'
insert into tb select '02' , 'k1087' , 'E公仔' , 6000 , '2008/05/10'
insert into tb select '02' , 'k1088' , 'B公仔' , 3000 , '2008/05/11'
insert into tb select '02' , 'k1089' , 'G公仔' , 9000 ,'2008/05/12'
insert into tb select '02' , 'k1084' , 'A公仔' , 6000 ,'2008/05/13'
godeclare @sql varchar(8000)
set @sql='select 拉号,产品编号,产品名称'
select @sql=@sql+',['+convert(varchar(10),日期,111)+']=sum(case 日期 when '''+convert(varchar(10),日期,111)+''' then 日生产数量 else 0 end)'
from (select distinct convert(varchar(10),日期,111)日期 from tb)a
set @sql=@sql+' from tb group by 拉号,产品编号,产品名称'
exec(@sql)
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(拉号 varchar(2),产口编号 varchar(5),产品名称 varchar(6),日生产数量 int,日期 smalldatetime)
Go
Insert into Ta
select '01','k1084','A公仔',1000,'2008/05/10' union all
select '01','k1088','B公仔',500,'2008/05/11' union all
select '01','k1085','C公仔',5000,'2008/05/12' union all
select '01','k1085','D公仔',10000,'2008/05/13' union all
select '02','k1087','E公仔',6000,'2008/05/10' union all
select '02','k1088','B公仔',3000,'2008/05/11' union all
select '02','k1089','G公仔',9000,'2008/05/12' union all
select '02','k1084','A公仔',6000,'2008/05/13'
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','')+'['+convert(char(10),日期,111)+'] = max(case when datediff(d,日期,'''+ convert(char(10),日期,111)+''') = 0 then 日生产数量 else 0 end)'
from (select distinct 日期 from ta) a
exec('select 拉号,产口编号,产品名称,'+ @s + ' from ta group by 拉号,产口编号,产品名称
order by 拉号,产品名称')--Result:
/*
拉号 产口编号 产品名称 2008/05/10 2008/05/11 2008/05/12 2008/05/13
---- ----- ------ ----------- ----------- ----------- -----------
01 k1084 A公仔 1000 0 0 0
01 k1088 B公仔 0 500 0 0
01 k1085 C公仔 0 0 5000 0
01 k1085 D公仔 0 0 0 10000
02 k1084 A公仔 0 0 0 6000
02 k1088 B公仔 0 3000 0 0
02 k1087 E公仔 6000 0 0 0
02 k1089 G公仔 0 0 9000 0
*/
--End