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 ..............
用 上一篇文章介绍的方法当然可以实现，但现在这里想要的列是固定的，不是动态的，就是12个月，所以也可以这样子来用：
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
举例，还是先创建测试数据表
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 ..............
用 上一篇文章介绍的方法当然可以实现，但现在这里想要的列是固定的，不是动态的，就是12个月，所以也可以这样子来用：
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
salesByMonthPIVOT (
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
salesByMonthPIVOT (
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 ...
同一个月份的数据累加到一起。
再给个微软官方的例子：
本文转自温景良(Jason)博客园博客，原文链接：http://www.cnblogs.com/wenjl520/archive/2009/12/09/1619897.html，如需转载请自行联系原作者
