SQL Server 2005中的PIVOT和UNPIVOT的例子
DECLARE @t TABLE(student varchar(50),class varchar(50),grade int)
INSERT INTO @t
SELECT '孙小美','数学',10 UNION ALL
SELECT '孙小美','语文',20 UNION ALL
SELECT '孙小美','英语',30 UNION ALL
SELECT '阿土伯','数学',40 UNION ALL
SELECT '阿土伯','语文',50 UNION ALL
SELECT '阿土伯','英语',60 UNION ALL
SELECT '小叮铛','数学',70 UNION ALL
SELECT '小叮铛','语文',80 UNION ALL
SELECT '小叮铛','英语',90
/* SQL Server 2000的交叉表*/
SELECT
student,
MAX(数学) AS 数学,
MAX(语文) AS 语文,
MAX(英语) AS 英语
FROM
(
SELECT
student,
CASE class WHEN '数学' THEN grade END AS 数学,
CASE class WHEN '语文' THEN grade END AS 语文,
CASE class WHEN '英语' THEN grade END AS 英语
FROM @t
) AS a
GROUP BY student
/* PIVOT */
SELECT student,数学,语文,英语 INTO #t FROM @t
PIVOT
(
MAX(grade) FOR class IN (数学,语文,英语)
) AS p
/* UNPION */
SELECT student,class,grade FROM #t
UNPIVOT
(
grade FOR class IN (数学,语文,英语)
) AS u
DROP TABLE #t
INSERT INTO @t
SELECT '孙小美','数学',10 UNION ALL
SELECT '孙小美','语文',20 UNION ALL
SELECT '孙小美','英语',30 UNION ALL
SELECT '阿土伯','数学',40 UNION ALL
SELECT '阿土伯','语文',50 UNION ALL
SELECT '阿土伯','英语',60 UNION ALL
SELECT '小叮铛','数学',70 UNION ALL
SELECT '小叮铛','语文',80 UNION ALL
SELECT '小叮铛','英语',90
/* SQL Server 2000的交叉表*/
SELECT
student,
MAX(数学) AS 数学,
MAX(语文) AS 语文,
MAX(英语) AS 英语
FROM
(
SELECT
student,
CASE class WHEN '数学' THEN grade END AS 数学,
CASE class WHEN '语文' THEN grade END AS 语文,
CASE class WHEN '英语' THEN grade END AS 英语
FROM @t
) AS a
GROUP BY student
/* PIVOT */
SELECT student,数学,语文,英语 INTO #t FROM @t
PIVOT
(
MAX(grade) FOR class IN (数学,语文,英语)
) AS p
/* UNPION */
SELECT student,class,grade FROM #t
UNPIVOT
(
grade FOR class IN (数学,语文,英语)
) AS u
DROP TABLE #t
posted on 2004-10-15 14:27 Goodspeed 阅读(1830) 评论(0) 编辑 收藏 举报