MSSQLSERVER PIVOT 行列转换详解

CREATE
 TABLE
 SalesByQuarter

( 
year
 INT
, 
-- 年份

 
quarter 
CHAR
(2), 
-- 季度

 
amount MONEY 
-- 总额

)

SET
 NOCOUNT 
ON

 
DECLARE
 @
index
 INT

 
DECLARE
 @q 
INT

 
SET
 @
index
 = 0

 
DECLARE
 @
year
 INT

 
while (@
index
 < 30)

 
BEGIN

  
SET
 @
year
 = 2005 + (@
index
 % 4)

  
SET
 @q = (
CAST
((RAND() * 500) 
AS
 INT
) % 4) + 1

  
INSERT
 INTO
 SalesByQuarter 
VALUES
 (@
year
, 
'Q'
 + 
CAST
(@q 
AS
 CHAR
(1)), RAND() * 10000.00)

  
SET
 @
index
 = @
index
 + 1

SELECT
 year
 as
 年份

 
, 
sum
 (
case
 when
 quarter = 
'Q1'
 then
 amount 
else
 0 
end
) 一季度

 
, 
sum
 (
case
 when
 quarter = 
'Q2'
 then
 amount 
else
 0 
end
) 二季度

 
, 
sum
 (
case
 when
 quarter = 
'Q3'
 then
 amount 
else
 0 
end
) 三季度

 
, 
sum
 (
case
 when
 quarter = 
'Q4'
 then
 amount 
else
 0 
end
) 四季度

FROM
 SalesByQuarter 
GROUP
 BY
 year
 ORDER
 BY
 year
 DESC

SELECT
 year
 as
 年份, Q1 
as
 一季度, Q2 
as
 二季度, Q3 
as
 三季度, Q4 
as
 四季度 
FROM
 SalesByQuarter PIVOT (
SUM
 (amount) 
FOR
 quarter 
IN
 (Q1, Q2, Q3, Q4) ) 
AS
 P 
ORDER
 BY
 YEAR
 DESC

SELECT
 [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]
--这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列(也就是某些天)

FROM
 WEEK_INCOME 
--这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误

PIVOT

(

 
SUM
(INCOME) 
for
 [week] 
in
([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
--这里是PIVOT第一步骤，也是核心的地方，进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值，是总和(sum)，还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”，其中一条的income是1000,另一条income是500，那么在这里使用sum，行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是说将week列的值分别转换成一个个列，也就是“以值变列”。但是需要转换成列的值有可能有很多，我们只想取其中几个值转换成列，那么怎样取呢？就是在in里面了，比如我此刻只想看工作日的收入，在in里面就只写“星期一”至“星期五”（注意，in里面是原来week列的值,"以值变列"）。总的来说，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来，就是说：将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列，这些列的值取income的总和。

)TBL
--别名一定要写

CREATE
 TABLE
 pvt (VendorID 
int
, Emp1 
int
, Emp2 
int
,

 
Emp3 
int
, Emp4 
int
, Emp5 
int
);

GO

INSERT
 INTO
 pvt 
VALUES
 (1,4,3,5,4,4);

INSERT
 INTO
 pvt 
VALUES
 (2,4,1,5,5,5);

INSERT
 INTO
 pvt 
VALUES
 (3,4,3,5,4,4);

INSERT
 INTO
 pvt 
VALUES
 (4,4,2,5,5,4);

INSERT
 INTO
 pvt 
VALUES
 (5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT
 VendorID, Employee, Orders

FROM

 
(
SELECT
 VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

 
FROM
 pvt) p

UNPIVOT

 
(Orders 
FOR
 Employee 
IN

  
(Emp1, Emp2, Emp3, Emp4, Emp5)

)
AS
 unpvt;

GO