动态行转列 ，高手留步，差一点

Question

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)
select @sql1 = @sql1 + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'
from (select distinct Mfgdate from T_ETL_PCodeCalCoee) as a
exec sp_executesql  @sql1
print @sql1 ----------------我 print  定义的@sql  语句，为何看不到结果---------------我怎么看不到结果

Answer 1

这样,你就能看到了:
select @sql1 = isnull(@sql1,'') + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'

Answer 2

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)  ----------------重点在这里---------------我怎么看不到结果
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 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(1 行受影响)*/ 

Answer 3

declare @sql1 nvarchar(4000)
set @sql1 = 'select mfgmonth '
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 = @sql1 + ' from T_ETL_PCodeCalCoee group by mfgmonth'
print @sql1
exec (@sql1)[Quote=引用 2 楼 qianguohua 的回复:]
引用 1 楼 qianjin036a 的回复:这样,你就能看到了:
SQL code
select @sql1 = isnull(@sql1,'') + 'max(case Mfgdate when ''' + Mfgdate + ''' then Coeetarget else 0 end) [' + Mfgdate + ']'能否写的具体点，菜鸟，新手
[/Quote]

Answer 4

declare @sql1 nvarchar(4000)
set @sql1 = 'select mfgmonth '
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 = @sql1 + ' from T_ETL_PCodeCalCoee group by mfgmonth'
print @sql1
exec (@sql1)
/*
mfgmonth,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
8,72.00,98.00,15.00,889.00,785.00,734.00,3435.00,45.00,76.00,87.00(1 行受影响)

Answer 5

--假设你按照mfgweek分组,如果不对,自己更改分组的列.
create table T_ETL_PCodeCalCoee
(
  Mfgdate nvarchar (100), 
  mfgweek nvarchar (100) , 
  mfgmonth nvarchar (100), 
  mfgyear nvarchar (100), 
  mfgQuarter nvarchar (100), 
  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(4000)
set @sql1 = 'select mfgweek '
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 = @sql1 + ' from T_ETL_PCodeCalCoee group by mfgweek'
exec (@sql1)
print @sql1drop table  T_ETL_PCodeCalCoee/*
mfgweek                                                                                              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             
---------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
W01                                                                                                  72.00                .00                  .00                  .00                  .00                  .00                  .00                  .00                  .00                  .00
W02                                                                                                  .00                  98.00                15.00                .00                  .00                  .00                  .00                  .00                  76.00                87.00
W03                                                                                                  .00                  .00                  .00                  889.00               .00                  .00                  .00                  .00                  .00                  .00
W04                                                                                                  .00                  .00                  .00                  .00                  785.00               .00                  .00                  .00                  .00                  .00
W05                                                                                                  .00                  .00                  .00                  .00                  .00                  734.00               .00                  .00                  .00                  .00
W06                                                                                                  .00                  .00                  .00                  .00                  .00                  .00                  3435.00              .00                  .00                  .00
W07                                                                                                  .00                  .00                  .00                  .00                  .00                  .00                  .00                  45.00                .00                  .00
*/

Answer 6

declare @sql1 nvarchar(4000)
set @sql1 = 'select mfgmonth '
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 = @sql1 + ' from T_ETL_PCodeCalCoee group by mfgmonth'
print @sql1
exec (@sql1)
/*
mfgmonth,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
8,72.00,98.00,15.00,889.00,785.00,734.00,3435.00,45.00,76.00,87.00(1 行受影响)