使用PIVOT访问SQL [英] Access SQL With PIVOT

问题描述

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

I'm using Access and attempting to duplicate some syntax I found on MSDN. Unfortunately it seems Access is having a problem with a FROM statement in my Syntax. I'm not sure why.

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

推荐答案

MS Access中枢转的语法不同于sql server.您必须使用 TRANSFORM 函数数据透视.

The syntax to pivot in MS Access is different from sql server. You have to use the TRANSFORM function to pivot data.

语法将与此类似:

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]

这篇关于使用PIVOT访问SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！