if OBJECT_ID('T_ETL_PCodeCalCoee') is not null
drop table T_ETL_PCodeCalCoee
go
create table T_ETL_PCodeCalCoee
(
Mfgdate nvarchar (4000),
mfgweek nvarchar (4000) ,
mfgmonth nvarchar (4000),
mfgyear nvarchar (4000),
mfgQuarter nvarchar (4000),
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 nvarchar(4000)
declare @sql2 nvarchar(4000)
declare @sql3 nvarchar(4000)
select @sql1 = isnull(@sql1+',','') + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec ('select '+ @sql1+'from T_ETL_PCodeCalCoee')select @sql2 = isnull(@sql2+',','') + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coee else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec ('select '+ @sql2+'from T_ETL_PCodeCalCoee')
-----------这里， 我想把 得到的 @sql1 合并 @sql2
select @sql3 =isnull(@sql3+',','')+ ('select '+ @sql1+'from T_ETL_PCodeCalCoee') union (('select '+ @sql2+'from T_ETL_PCodeCalCoee') a)exec @sql3
-------------事实上，我想做的是，安Coeetarget，Coee，Mtdcoee ，Mtdcoee MtdcoeeExcludeIdle 按照 日期8/1，8/2 的值显示出来，谢谢，高手，赐教，新手学习中，，
(
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)declare @sql varchar(8000)
set @sql = 'select Coeetarget,Coee,Mtdcoee,Mtdcoee,MtdcoeeExcludeIdle'
select @sql = @sql + ' , max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
set @sql = @sql + ' from T_ETL_PCodeCalCoee group by Coeetarget,Coee,Mtdcoee,Mtdcoee,MtdcoeeExcludeIdle'
exec(@sql) drop table T_ETL_PCodeCalCoee/*
Coeetarget Coee Mtdcoee Mtdcoee MtdcoeeExcludeIdle 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
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
15.00 71.00 71.00 71.00 77.00 .00 .00 15.00 .00 .00 .00 .00 .00 .00 .00
45.00 71.00 71.00 71.00 77.00 .00 .00 .00 .00 .00 .00 .00 45.00 .00 .00
72.00 70.00 70.00 70.00 79.00 72.00 .00 .00 .00 .00 .00 .00 .00 .00 .00
76.00 71.00 71.00 71.00 77.00 .00 .00 .00 .00 .00 .00 .00 .00 76.00 .00
87.00 72.00 72.00 72.00 77.00 .00 .00 .00 .00 .00 .00 .00 .00 .00 87.00
98.00 73.00 73.00 73.00 77.00 .00 98.00 .00 .00 .00 .00 .00 .00 .00 .00
734.00 74.00 74.00 74.00 79.00 .00 .00 .00 .00 .00 734.00 .00 .00 .00 .00
785.00 73.00 73.00 73.00 78.00 .00 .00 .00 .00 785.00 .00 .00 .00 .00 .00
889.00 72.00 72.00 72.00 77.00 .00 .00 .00 889.00 .00 .00 .00 .00 .00 .00
3435.00 70.00 70.00 70.00 78.00 .00 .00 .00 .00 .00 .00 3435.00 .00 .00 .00
*/
意思，好像反了， 应该是以日期。位列的形式，显示。相当于每日报表
Coeetarget 行 下面的 列 8/1 列 8/2
declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(4000)
declare @sql3 nvarchar(4000)
select @sql1 = isnull(@sql1+',','') + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec ('select '+ @sql1+'from T_ETL_PCodeCalCoee')--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
--72.00 98.00 15.00 889.00 785.00 734.00 3435.00 45.00 76.00 87.00
select @sql2 = isnull(@sql2+',','') + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coee else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec ('select '+ @sql2+'from T_ETL_PCodeCalCoee')--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
--70.00 73.00 71.00 72.00 73.00 74.00 70.00 71.00 71.00 72.00
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec ('select '+ @sql1+'from (select Mfgdate,Coee,Mtdcoee,Mtdcoee,MtdcoeeExcludeIdle from T_ETL_PCodeCalCoee union all select Coeetarget as Mfgdate,Coee,Mtdcoee,Mtdcoee,MtdcoeeExcludeIdle from T_ETL_PCodeCalCoee)t ')
(
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)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'' 项目, ' + 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'' 项目, ' + 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'' 项目, ' + 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'' 项目, ' + substring(@sql4,2,len(@sql4)) + ' from T_ETL_PCodeCalCoee 'exec(@sql1 + @sql2 + @sql3 + @sql4) drop table T_ETL_PCodeCalCoee/*
项目 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
*/
drop table T_ETL_PCodeCalCoee
go
create table T_ETL_PCodeCalCoee
(
Mfgdate nvarchar (4000),
mfgweek nvarchar (4000) ,
mfgmonth nvarchar (4000),
mfgyear nvarchar (4000),
mfgQuarter nvarchar (4000),
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 nvarchar(4000)select @sql1 = isnull(@sql1+',','') +
'max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'
from
(select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec ('select '+ @sql1+'from
(select Mfgdate,mfgweek,mfgmonth,mfgyear,mfgQuarter,Coeetarget,CoeeTargetExcludeIdle,coeeExcludeIdle,Mtdcoee,MtdcoeeExcludeIdle from T_ETL_PCodeCalCoee
union all
select Mfgdate,mfgweek,mfgmonth,mfgyear,mfgQuarter,coee as Coeetarget,CoeeTargetExcludeIdle,coeeExcludeIdle,Mtdcoee,MtdcoeeExcludeIdle from T_ETL_PCodeCalCoee)t')/*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
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
72.00 98.00 71.00 889.00 785.00 734.00 3435.00 71.00 76.00 87.00(1 行受影响)*/