当前位置:Gxlcms > mysql > SQLServer2005PIVOT用法示例

SQLServer2005PIVOT用法示例

时间:2021-07-01 10:21:17 帮助过:36人阅读

最近学习 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,'200

  最近学习 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

人气教程排行