关于sqlserver中的pivot和 unpivot

在sqlsever2005后有次关键字；

1、pivot( 使绕枢轴旋转，行转列)

语法：

 

以下是带批注的 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>;

==============

测试实例：

DECLARE @tmpTable TABLE (
p_name NVARCHAR(30),
p_curse NVARCHAR(30),
p_grade DECIMAL(18,2),
p_age INT,
p_sex BIT
)

INSERT INTO @tmpTable
SELECT 'jim','chinese',80,18,0
UNION
SELECT 'tim','chinese',90,18,1
UNION
SELECT 'xim','chinese',100,18,0
UNION
SELECT 'jim','math',80,18,0
UNION
SELECT 'tim','math',90,18,0
UNION
SELECT 'xim','math',100,18,0


SELECT * FROM @tmpTable


SELECT * 
FROM @tmpTable 
PIVOT(MAX (p_grade)  FOR  p_curse IN  (math,chinese))a 


SELECT p_name,math,chinese
FROM @tmpTable  --这里没有对原有表的列进行投影，导致最终转置后某些列的值为空
PIVOT(MAX (p_grade)  FOR  p_curse IN  (math,chinese))a


-- 上面两个查询可以看出，对于某些列中空值，在最终的投影进行是不起作用的
-- 一定要在pivot基于的数据集进行先进行投影处理才会起作用，即from后面的结果集


SELECT *
FROM
(SELECT p_name,p_curse,p_grade FROM @tmpTable) a --这里对原有表的列进行投影，从而去除某些列的空值
PIVOT (MAX(p_grade) FOR p_curse IN (math,chinese)) b

 

以下是执行结果情况

原始数据集

第一个pivot

第二个pivot

第三个pivot

 

2.unpivot(列转行)

DECLARE @tmpTable1 TABLE (
p_name NVARCHAR(30),
math DECIMAL(18,2),
chinese DECIMAL(18,2),
p_sex BIT,
p_age INT
)


INSERT INTO @tmpTable1
SELECT 'jim',80,90,0,18
UNION
SELECT 'tim',90,90,1,29
UNION
SELECT 'tom',100,100,0,30


--原始数据
SELECT * FROM @tmpTable1 


--转置后数据
SELECT p_name,curse,grade --转置后数据表投影
FROM 
(SELECT p_name,math,chinese FROM @tmpTable1) a --转置的源数据集
UNPIVOT 
( 
grade --转置后的
FOR
curse IN (math,chinese) --curse为列转化成行后对应的列名（即把几个列集中到一列） 转置的列集合，将转化为对应行的值
) b

 

==============

原始数据集

转置后的数据集