SQL Server 2005 PIVOT 用法示例
SQL Server 2005 PIVOT 用法示例
最近学习 SQL 2005的新特性，可能很多人都知道了，我反应比较慢，过两天都2010年了，才开始学2005.总结一下：表如下：
create table tb(ID varchar(50),CName varchar(50),Amount money,ActionDate datetime)
insert into tb values('001','a',2000.00,'2008-02-28')
insert into tb values('001','a',1560.00,'2008-03-28')
insert into tb values('001','a',2040.00,'2008-01-28')
insert into tb values('001','a',1003.00,'2008-04-28')
insert into tb values('101','b',3100.00,'2008-03-28')
insert into tb values('101','b',4040.00,'2008-01-28')
insert into tb values('101','b',5003.00,'2008-04-28')
insert into tb values('101','b',2400.00,'2008-02-28')
insert into tb values('001','a',15000.00,'2007-12-21')
insert into tb values('101','b',22400.00,'2007-12-21')
---------要将数据转换成如下：
ID CName 2007-12 2008-01 2008-02 2008-03 2008-04
001 a 15000.00 2040.00 2000.00 1560.00 1003.00
101 b 22400.00 4040.00 2400.00 3100.00 5003.00
实现步骤如下：
-----
select ID,CName,sum(amount) as amount,left(convert(varchar(20),Actiondate,120),7) as Actiondate
into #tmp
from tb
group by ID,CName,left(convert(varchar(20),Actiondate,120),7)
declare @sql varchar(max), @sqlAll nvarchar(max)
select @sql=''
select @sql=@sql+'['+Actiondate+'],'
from #tmp group by Actiondate
select @sql=left(@sql,len(@sql)-1)
print @sql
select @sqlAll='
select * from #tmp s
pivot
(sum(amount)
for Actiondate in ('+@sql+')
) as P order by ID,CName '
print @sqlAll
------Step4
exec sp_executesql @sqlAll
drop table #tmp