sql 行转列

一、先建立测试数据    

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>;