Sql Server2005中行转列的新运算符与通用行转列方法

闲逛CSDN时，无意中看到kkun_3yue3大哥在回答一个问题的时候用到Pivot运算符来进行行转列

由于之前并不知道该运算符的具体用法(应该是根据就不知道有这东西，哈哈)，于是查了下GOOGLE，发现该运算符是在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

通用方法：

ALTER Proc Row2Col2
as
    Declare @Sql as nvarchar(2000)
    Set @Sql = 'Select ID,CName,'
    Select  @Sql = @Sql + 'Sum(case When SubString(Convert(varchar(20),ActionDate,120),1,10) = '''+SubString(Convert(varchar(20),ActionDate,120),1,10)+''' Then Amount else 0 End ) as '''+SubString(Convert(varchar(20),ActionDate,120),1,10)+''','
           From SamTest Group By SubString(Convert(varchar(20),ActionDate,120),1,10)
    Set @Sql = SubString(@Sql,1,len(@Sql) -1)
    Set @Sql = @Sql + ' From SamTest Group By ID,CName'

   
    Exec sp_Executesql @Sql

 

Pivot函运算符用法：

 

alter Proc Row2Col
as

    Select ID,CName,Sum(Amount) as Amount,SubString(Convert(varchar(20),ActionDate,120),1,10) as ActionDate
    into #tmp
    From SamTest
    Group By ID,CName,SubString(Convert(varchar(20),ActionDate,120),1,10)

    Declare @UseCol as nvarchar(500)
    Declare @Sql   AS nVARCHAR(1000)
    Set @UseCol = ''
    Set @Sql = ''

    Select @UseCol = @UseCol + '['+ActionDate+'],' From #tmp Group By ActionDate

    Select @UseCol = SubString(@UseCol,1,len(@UseCol) -1)
    
    Select @Sql = 'Select * from #tmp s  pivot (Sum(Amount) For ActionDate In('+@UseCol+')) as p Order By ID,CName'

    exec sp_executesql @Sql
  
    Drop Table #tmp

 

 

 

个人感觉:

通用方法主要是用Case When 来进行比较的，比较繁琐

Piovd取代了CASE WHEN 的做法了，而且取消了Group By 分组哦，使逻辑性更容易理解

 

转载于:https://www.cnblogs.com/zyzhang/archive/2010/07/07/1772841.html