SQL Server 2005 PIVOT 用法示例
來(lái)源：程序員人生 發(fā)布時(shí)間：2014-05-09 15:06:21 閱讀次數(shù)：3045次
最近學(xué)習(xí) SQL 2005的新特性，可能很多人都知道了，我反應(yīng)比較慢，過(guò)兩天都2010年了，才開(kāi)始學(xué)2005.總結(jié)一下：
表如下：
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')
---------要將數(shù)據(jù)轉(zhuǎn)換成如下：
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
實(shí)現(xiàn)步驟如下：
-----------------------------------------Step 1 -----------------------------------------------
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)
--------------------------------------------Step2 ------------------------------------------------
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
---------------------------------------------Step3 ---------------------------------------------
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
----------------------------------------------------------------------------------
