使用PIVOT访问SQL

Question

我正在使用Access，并尝试复制在MSDN上发现的某些语法。不幸的是，Access语法中的FROM语句似乎有问题。我不知道为什么。

SELECT YEAR, HC
FROM
(
SELECT [Retention & Graduation].Year, [Retention & Graduation].Status, [Retention & Graduation].Hc
FROM [Retention & Graduation]
) AS X

PIVOT
(
SUM([HC]) FOR [STATUS] IN ([GONE], [HERE], [GRAD])
) AS Xx

Answer 1

MS Access中枢转的语法与sql server不同。您必须使用该TRANSFORM功能来旋转数据。

语法将类似于以下内容：

TRANSFORM SUM([Retention & Graduation].Hc)
SELECT [Retention & Graduation].Year, 
  [Retention & Graduation].Status, 
  [Retention & Graduation].Hc
FROM [Retention & Graduation]
GROUP BY [Retention & Graduation].Year
PIVOT [Retention & Graduation].[STATUS]