SQL2005/2008中实现行转列的2种方法

SQL2005/2008中实现行转列的2种方法

标签： SQL2005  数据库  休闲  职场

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律责任。 http://liweibird.blog.51cto.com/631764/250354

一、先建立测试数据    

  
  

   
   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)
  
  

 

二、对上述数据进行行转列

 

方法一：大家都常用的方法：select case

 

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

 

方法二：SQL2005/2008中的 Pivot 方法

 

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 MyPivot

ORDER BY Year

 

对上述Pivot语法结构的详细解释如下：

 

Povit：旋转

Pivoted：旋转的

Query：查询

Produce：生成、产生

alias：别名、化名

aggregation：聚合

optional：可选择的

clause：子句

 

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关  PIVOT 语法的完整说明，请参阅 FROM (Transact-SQL)。

以下是带批注的  PIVOT 语法。

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

     AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

 

本文出自 “跟着兴趣走” 博客，请务必保留此出处http://liweibird.blog.51cto.com/631764/250354