表Manual_Week_Trend数据
week area1 area2 price
09-03～09-09 232 333 33
09-10～09-16 12 1212 211
09-17～09-23 233 333 3333
09-24～09-30 34 555 23
10-1～10-7 56 88 444
10-8～10-15 334 44 444
转为如下
内容 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
------解决方案--------------------
declare @sql varchar(8000)
set @sql= ' '
select @[email protected]+',max(case when [week]= '+rtrim([week])+
' then area1 end)as ['+rtrim([week])+'] '
from Manual_Week_Trend
select @sql='select''area1''as [week][email protected]+ 'from Manual_Week_Trend'
exec(@sql)
------解决方案--------------------
2000:
可用临时表实现
2005可用pivot：
http://blog.csdn.net/roy_88/archive/2007/02/13/1509413.aspx
------解决方案--------------------
- SQL code
use testgocreate 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 goselect [week],area1,ColName='area1' into #t from Manual_Week_Trendunion all select [week],area2,ColName='area2' from Manual_Week_Trendunion all select [week],price,ColName='price' from Manual_Week_Trendgodeclare @s nvarchar(4000)set @s=''select @[email protected]+',['+[week]+']=sum(case when [week]='+quotename([week],'''')+' then area1 else 0 end)'from #t group by [week] exec('select [email protected]+' 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 334area2 333 1212 333 555 88 44price 33 211 3333 23 444 444
------解决方案--------------------
- SQL code
静态方法:use testgocreate 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 goselect 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,ColName='price' from Manual_Week_Trend)tgroup by ColNameColName 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 334area2 333 1212 333 555 88 44price 33 211 3333 23 444 444（所影响的行数为 3 行）