DECLARE @t TABLE(student varchar(50),class varchar(50),grade int) INSERTINTO @t SELECT'孙小美','数学',10UNIONALL SELECT'孙小美','语文',20UNIONALL SELECT'孙小美','英语',30UNIONALL SELECT'阿土伯','数学',40UNIONALL SELECT'阿土伯','语文',50UNIONALL SELECT'阿土伯','英语',60UNIONALL SELECT'小叮铛','数学',70UNIONALL SELECT'小叮铛','语文',80UNIONALL SELECT'小叮铛','英语',90 /**//* SQL Server 2000的交叉表*/ SELECT student, MAX(数学) AS 数学, MAX(语文) AS 语文, MAX(英语) AS 英语 FROM ( SELECT student, CASE class WHEN'数学'THEN grade ENDAS 数学, CASE class WHEN'语文'THEN grade ENDAS 语文, CASE class WHEN'英语'THEN grade ENDAS 英语 FROM @t ) AS a GROUPBY 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 DROPTABLE #t