Sql Server 2005 行转列的实现(横排)

robin

　　SQL SERVER 2005中新增加了两个关系运算符 PIVOT/ UNPIVOT，能够实现表中的列转换到行，以及行到列的转换工作。   
举例，还是先创建测试数据表
　　Create TABLE sales.salesByMonth 

• ( 
  
• year char(4), 
  
• month char(3), 
  
• amount money, 
  
• PRIMARY KEY (year, month) 
  
• ) 
  
• 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Jan', 789.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Feb', 389.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Mar', 8867.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Apr', 778.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','May', 78.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Jun', 9.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Jul', 987.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Aug', 866.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Sep', 7787.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Oct', 85576.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Nov', 855.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2004','Dec', 5878.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2005','Jan', 7.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2005','Feb', 6868.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2005','Mar', 688.0000) 
  
• Insert INTO sales.salesByMonth (year, month, amount) 
  
• VALUES('2005','Apr', 9897.0000) 
  

　　我们想要得到类似这样的结果：   
Year  Jan            Feb             Mar       …………..     
—– ———- ———– ———–     
2004 789.0000 389.0000     8867.0000 ………….     
2005 7.0000     6868.0000   688.0000 …………..

• Select year, 
  
• SUM(case when month = 'Jan' then amount else 0 end) AS 'Jan', 
  
• SUM(case when month = 'Feb' then amount else 0 end) AS 'Feb', 
  
• SUM(case when month = 'Mar' then amount else 0 end) AS 'Mar', 
  
• SUM(case when month = 'Apr' then amount else 0 end) AS 'Apr', 
  
• SUM(case when month = 'May' then amount else 0 end) AS 'May', 
  
• SUM(case when month = 'Jun' then amount else 0 end) AS 'Jun', 
  
• SUM(case when month = 'Jul' then amount else 0 end) AS 'Jul', 
  
• SUM(case when month = 'Aug' then amount else 0 end) AS 'Aug', 
  
• SUM(case when month = 'Sep' then amount else 0 end) AS 'Sep', 
  
• SUM(case when month = 'Oct' then amount else 0 end) AS 'Oct', 
  
• SUM(case when month = 'Nov' then amount else 0 end) AS 'Nov', 
  
• SUM(case when month = 'Dec' then amount else 0 end) AS 'Dec'
  
• FROM sales.salesByMonth 
  
• GROUP by year
  

　　
但这样事实上还是相当麻烦的，现在SQLSERVER2005中有更方便的实现方法。
　　Select Year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec] 

• FROM ( 
  
• Select year, amount, month
  
• FROM sales.salesByMonth ) AS salesByMonth 
  
• PIVOT ( SUM(amount) FOR month IN
  
•   ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) 
  
•   ) AS ourPivot 
  
• orDER BY Year
  

　　就是这样，很简单的用法，效果是完全一样的。   
我们再尝试一下把year去掉：
　　Select [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec] 

• FROM ( Select amount, month
  
• FROM sales.salesByMonth ) AS salesByMonth 
  
• PIVOT ( SUM(amount) FOR month IN
  
• ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) 
  
• ) AS ourPivot 
  

　　得到的结果是：   
Jan                 Feb                 Mar             …     
———- ———— ———–     
796.0000     7257.0000     9555.0000     …   
同一个月份的数据累加到一起。   
再给个微软官方的例子：