34,591
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('T_ETL_PCodeCalCoee') is not null
drop table T_ETL_PCodeCalCoee
go
create table T_ETL_PCodeCalCoee
(
Mfgdate nvarchar (10),
mfgweek nvarchar (10) ,
mfgmonth nvarchar (10),
mfgyear nvarchar (10),
mfgQuarter nvarchar (10),
Coeetarget decimal(18,2),
CoeeTargetExcludeIdle decimal(18,2),
coee decimal(18,2),
coeeExcludeIdle decimal(18,2),
Mtdcoee decimal(18,2),
MtdcoeeExcludeIdle decimal(18,2)
)
insert into T_ETL_PCodeCalCoee values ('2011/8/1','W01','8','2011','3',72,80,70,79,70,79)
insert into T_ETL_PCodeCalCoee values ('2011/8/2','W02','8','2011','3',15,80,71,77,71,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/3','W03','8','2011','3',889,80,72,77,72,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/4','W04','8','2011','3',785,80,73,78,73,78)
insert into T_ETL_PCodeCalCoee values ('2011/8/5','W05','8','2011','3',734,80,74,79,74,79)
insert into T_ETL_PCodeCalCoee values ('2011/8/6','W06','8','2011','3',3435,80,70,78,70,78)
insert into T_ETL_PCodeCalCoee values ('2011/8/7','W07','8','2011','3',45,80,71,77,71,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/8','W02','8','2011','3',76,80,71,77,71,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/9','W02','8','2011','3',87,80,72,77,72,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/10','W02','8','2011','3',98,80,73,77,73,77)
select * from T_ETL_PCodeCalCoee
declare @sql1 varchar(8000)
set @sql1 = ''
select @sql1 = @sql1 + ',max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql1 = 'select ''Coeetarget'' gg, ' + substring(@sql1,2,len(@sql1)) + ' from T_ETL_PCodeCalCoee '
declare @sql2 varchar(8000)
set @sql2 = ''
select @sql2 = @sql2 + ',max(case Mfgdate when ''' + Mfgdate + ''' then Coee else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql2 = 'union all select ''Coee'' gg, ' + substring(@sql2,2,len(@sql2)) + ' from T_ETL_PCodeCalCoee '
declare @sql3 varchar(8000)
set @sql3 = ''
select @sql3 = @sql3 + ',max(case Mfgdate when ''' + Mfgdate + ''' then Mtdcoee else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql3 = 'union all select ''Mtdcoee'' gg, ' + substring(@sql3,2,len(@sql3)) + ' from T_ETL_PCodeCalCoee '
declare @sql4 varchar(8000)
set @sql4 = ''
select @sql4 = @sql4 + ',max(case Mfgdate when ''' + Mfgdate + ''' then MtdcoeeExcludeIdle else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql4 = 'union all select ''MtdcoeeExcludeIdle'' gg, ' + substring(@sql4,2,len(@sql4)) + ' from T_ETL_PCodeCalCoee '
----此为 aaaaaa (用户名:dawugui) 高手
select @sql1 ----这个是我想看效果自己加的
select @sql2 ----这个是我想看效果自己加的
select @sql3 ----这个是我想看效果自己加的
select @sql4 ----这个是我想看效果自己加的
-----此为 ----此为 aaaaaa (用户名:dawugui) 高手
exec(@sql1 + @sql2 + @sql3 + @sql4)
----这个是我想看效果自己加的
select 'Coeetarget' gg, max(case Mfgdate when '2011/8/1' then Coeetarget else 0 end) [2011/8/1],max(case Mfgdate when '2011/8/10' then Coeetarget else 0 end) [2011/8/10],max(case Mfgdate when '2011/8/2' then Coeetarget else 0 end) [2011/8/2],max(case Mfgdate when '2011/8/3' then Coeetarget else 0 end) [2011/8/3],max(case Mfgdate when '2011/8/4' then Coeetarget else 0 end) [2011/8/4],max(case Mfgdate when '2011/8/5' then Coeetarget else 0 end) [2011/8/5],max(case Mfgdate when '2011/8/6' then Coeetarget else 0 end) [2011/8/6],max(case Mfgdate when '2011/8/7' then Coeetarget else 0 end) [2011/8/7],max(case Mfgdate when '2011/8/8' then Coeetarget else 0 end) [2011/8/8],max(case Mfgdate when '2011/8/9' then Coeetarget else 0 end) [2011/8/9] from T_ETL_PCodeCalCoee
union all select 'Coee' gg, max(case Mfgdate when '2011/8/1' then Coee else 0 end) [2011/8/1],max(case Mfgdate when '2011/8/10' then Coee else 0 end) [2011/8/10],max(case Mfgdate when '2011/8/2' then Coee else 0 end) [2011/8/2],max(case Mfgdate when '2011/8/3' then Coee else 0 end) [2011/8/3],max(case Mfgdate when '2011/8/4' then Coee else 0 end) [2011/8/4],max(case Mfgdate when '2011/8/5' then Coee else 0 end) [2011/8/5],max(case Mfgdate when '2011/8/6' then Coee else 0 end) [2011/8/6],max(case Mfgdate when '2011/8/7' then Coee else 0 end) [2011/8/7],max(case Mfgdate when '2011/8/8' then Coee else 0 end) [2011/8/8],max(case Mfgdate when '2011/8/9' then Coee else 0 end) [2011/8/9] from T_ETL_PCodeCalCoee
union all select 'Mtdcoee' gg, max(case Mfgdate when '2011/8/1' then Mtdcoee else 0 end) [2011/8/1],max(case Mfgdate when '2011/8/10' then Mtdcoee else 0 end) [2011/8/10],max(case Mfgdate when '2011/8/2' then Mtdcoee else 0 end) [2011/8/2],max(case Mfgdate when '2011/8/3' then Mtdcoee else 0 end) [2011/8/3],max(case Mfgdate when '2011/8/4' then Mtdcoee else 0 end) [2011/8/4],max(case Mfgdate when '2011/8/5' then Mtdcoee else 0 end) [2011/8/5],max(case Mfgdate when '2011/8/6' then Mtdcoee else 0 end) [2011/8/6],max(case Mfgdate when '2011/8/7' then Mtdcoee else 0 end) [2011/8/7],max(case Mfgdate when '2011/8/8' then Mtdcoee else 0 end) [2011/8/8],max(case Mfgdate when '2011/8/9' then Mtdcoee else 0 end) [2011/8/9] from T_ETL_PCodeCalCoee
union all select 'MtdcoeeExcludeIdle' gg, max(case Mfgdate when '2011/8/1' then MtdcoeeExcludeIdle else 0 end) [2011/8/1],max(case Mfgdate when '2011/8/10' then MtdcoeeExcludeIdle else 0 end) [2011/8/10],max(case Mfgdate when '2011/8/2' then MtdcoeeExcludeIdle else 0 end) [2011/8/2],max(case Mfgdate when '2011/8/3' then MtdcoeeExcludeIdle else 0 end) [2011/8/3],max(case Mfgdate when '2011/8/4' then MtdcoeeExcludeIdle else 0 end) [2011/8/4],max(case Mfgdate when '2011/8/5' then MtdcoeeExcludeIdle else 0 end) [2011/8/5],max(case Mfgdate when '2011/8/6' then MtdcoeeExcludeIdle else 0 end) [2011/8/6],max(case Mfgdate when '2011/8/7' then MtdcoeeExcludeIdle else 0 end) [2011/8/7],max(case Mfgdate when '2011/8/8' then MtdcoeeExcludeIdle else 0 end) [2011/8/8],max(case Mfgdate when '2011/8/9' then MtdcoeeExcludeIdle else 0 end) [2011/8/9] from T_ETL_PCodeCalCoee
----显示的结果
--gg 2011/8/1 2011/8/10 2011/8/2 2011/8/3 2011/8/4 2011/8/5 2011/8/6 2011/8/7 2011/8/8 2011/8/9
--Coeetarget 72.00 98.00 15.00 889.00 785.00 734.00 3435.00 45.00 76.00 87.00
--Coee 70.00 73.00 71.00 72.00 73.00 74.00 70.00 71.00 71.00 72.00
--Mtdcoee 70.00 73.00 71.00 72.00 73.00 74.00 70.00 71.00 71.00 72.00
--MtdcoeeExcludeIdle 79.00 77.00 77.00 77.00 78.00 79.00 78.00 77.00 77.00 77.00
为何 2011/8/10 号,跑2011/8/1 和2011/8/2 中间,其他很正常
--应该是union all 后的排序问题吧,你把取得的结果再按你需求的某行排序就行了.
create table T_ETL_PCodeCalCoee
(
Mfgdate nvarchar (10),
mfgweek nvarchar (10) ,
mfgmonth nvarchar (10),
mfgyear nvarchar (10),
mfgQuarter nvarchar (10),
Coeetarget decimal(18,2),
CoeeTargetExcludeIdle decimal(18,2),
coee decimal(18,2),
coeeExcludeIdle decimal(18,2),
Mtdcoee decimal(18,2),
MtdcoeeExcludeIdle decimal(18,2)
)
insert into T_ETL_PCodeCalCoee values ('2011/8/1','W01','8','2011','3',72,80,70,79,70,79)
insert into T_ETL_PCodeCalCoee values ('2011/8/2','W02','8','2011','3',15,80,71,77,71,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/3','W03','8','2011','3',889,80,72,77,72,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/4','W04','8','2011','3',785,80,73,78,73,78)
insert into T_ETL_PCodeCalCoee values ('2011/8/5','W05','8','2011','3',734,80,74,79,74,79)
insert into T_ETL_PCodeCalCoee values ('2011/8/6','W06','8','2011','3',3435,80,70,78,70,78)
insert into T_ETL_PCodeCalCoee values ('2011/8/7','W07','8','2011','3',45,80,71,77,71,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/8','W02','8','2011','3',76,80,71,77,71,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/9','W02','8','2011','3',87,80,72,77,72,77)
insert into T_ETL_PCodeCalCoee values ('2011/8/10','W02','8','2011','3',98,80,73,77,73,77)
select * from T_ETL_PCodeCalCoee
declare @sql1 varchar(8000)
set @sql1 = ''
select @sql1 = @sql1 + ',max(case cast(Mfgdate as datetime) when ''' + convert(varchar(10),Mfgdate,120) + ''' then Coeetarget else 0 end) [' + convert(varchar(10),Mfgdate,120) + ']'
from (select distinct cast(Mfgdate as datetime) Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql1 = 'select ''Coeetarget'' gg, ' + substring(@sql1,2,len(@sql1)) + ' from T_ETL_PCodeCalCoee '
declare @sql2 varchar(8000)
set @sql2 = ''
select @sql2 = @sql2 + ',max(case cast(Mfgdate as datetime) when ''' + convert(varchar(10),Mfgdate,120) + ''' then Coee else 0 end) [' + convert(varchar(10),Mfgdate,120) + ']'
from (select distinct cast(Mfgdate as datetime) Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql2 = 'union all select ''Coee'' gg, ' + substring(@sql2,2,len(@sql2)) + ' from T_ETL_PCodeCalCoee '
declare @sql3 varchar(8000)
set @sql3 = ''
select @sql3 = @sql3 + ',max(case cast(Mfgdate as datetime) when ''' + convert(varchar(10),Mfgdate,120) + ''' then Mtdcoee else 0 end) [' + convert(varchar(10),Mfgdate,120) + ']'
from (select distinct cast(Mfgdate as datetime) Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql3 = 'union all select ''Mtdcoee'' gg, ' + substring(@sql3,2,len(@sql3)) + ' from T_ETL_PCodeCalCoee '
declare @sql4 varchar(8000)
set @sql4 = ''
select @sql4 = @sql4 + ',max(case cast(Mfgdate as datetime) when ''' + convert(varchar(10),Mfgdate,120) + ''' then MtdcoeeExcludeIdle else 0 end) [' + convert(varchar(10),Mfgdate,120) + ']'
from (select distinct cast(Mfgdate as datetime) Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql4 = 'union all select ''MtdcoeeExcludeIdle'' gg, ' + substring(@sql4,2,len(@sql4)) + ' from T_ETL_PCodeCalCoee '
exec(@sql1 + @sql2 + @sql3 + @sql4)
drop table T_ETL_PCodeCalCoee
/*
gg 2011-08-01 2011-08-02 2011-08-03 2011-08-04 2011-08-05 2011-08-06 2011-08-07 2011-08-08 2011-08-09 2011-08-10
------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
Coeetarget 72.00 15.00 889.00 785.00 734.00 3435.00 45.00 76.00 87.00 98.00
Coee 70.00 71.00 72.00 73.00 74.00 70.00 71.00 71.00 72.00 73.00
Mtdcoee 70.00 71.00 72.00 73.00 74.00 70.00 71.00 71.00 72.00 73.00
MtdcoeeExcludeIdle 79.00 77.00 77.00 78.00 79.00 78.00 77.00 77.00 77.00 77.00
*/