在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
==============
原始数据集
转置后的数据集