use test
go
create table Manual_Week_Trend([week] nvarchar(20),area1 int ,        area2   int,    price  int)
insert Manual_Week_Trend select '09-03～09-09',    232   ,       333,        33 
insert Manual_Week_Trend select '09-10～09-16',     12  ,       1212 ,      211 
insert Manual_Week_Trend select '09-17～09-23',     233,         333 ,     3333 
insert Manual_Week_Trend select '09-24～09-30',     34,      555    ,  23 
insert Manual_Week_Trend select '10-1～10-7',     56 ,      88      ,444 
insert Manual_Week_Trend select '10-8～10-15',    334,        44    ,  444 
go

select [week],area1,ColName='area1' into #t from Manual_Week_Trend
union all 
select [week],area2,ColName='area2' from Manual_Week_Trend
union all 
select [week],price,ColName='price' from Manual_Week_Trend

go
declare @s nvarchar(4000)
set @s=''
select @s=@s+',['+[week]+']=sum(case when [week]='+quotename([week],'''')+' then area1 else 0 end)'
from #t group by [week] 
exec('select ColName'+@s+' from #t group by ColName')


ColName 09-03～09-09 09-10～09-16 09-17～09-23 09-24～09-30 10-1～10-7   10-8～10-15  
------- ----------- ----------- ----------- ----------- ----------- ----------- 
area1   232         12          233         34          56          334
area2   333         1212        333         555         88          44
price   33          211         3333        23          444         444

------解决方案--------------------
SQL code

静态方法:
use test
go
create table Manual_Week_Trend([week] nvarchar(20),area1 int ,        area2   int,    price  int)
insert Manual_Week_Trend select '09-03～09-09',    232   ,       333,        33 
insert Manual_Week_Trend select '09-10～09-16',     12  ,       1212 ,      211 
insert Manual_Week_Trend select '09-17～09-23',     233,         333 ,     3333 
insert Manual_Week_Trend select '09-24～09-30',     34,      555    ,  23 
insert Manual_Week_Trend select '10-1～10-7',     56 ,      88      ,444 
insert Manual_Week_Trend select '10-8～10-15',    334,        44    ,  444 
go


select 
    ColName,
    [09-03～09-09]=sum(case when [week]='09-03～09-09' then area1 else 0 end),
    [09-10～09-16]=sum(case when [week]='09-10～09-16' then area1 else 0 end),
    [09-17～09-23]=sum(case when [week]='09-17～09-23' then area1 else 0 end),
    [09-24～09-30]=sum(case when [week]='09-24～09-30' then area1 else 0 end),
    [10-1～10-7]=sum(case when [week]='10-1～10-7' then area1 else 0 end),
    [10-8～10-15]=sum(case when [week]='10-8～10-15' then area1 else 0 end) 
from (select [week],area1,ColName='area1'  from Manual_Week_Trend
    union all 
    select [week],area2,ColName='area2' from Manual_Week_Trend
    union all 
    select [week],price,ColNa