在Access SQL中取消透视 [英] Unpivot in Access SQL

问题描述

大家好，我试图在MS Access的SQL中使用unpivot，但我在网上找到了以下代码:

Hi guys I'm trying to use unpivot in SQL on MS Access and I found the following code online:

SELECT CustomerID, Phone
FROM
(
  SELECT CustomerID, Phone1, Phone2, Phone3 
  FROM dbo.CustomerPhones
) AS cp
UNPIVOT 
(
  Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS up;

在此网页上: https://www. mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/

但是，当我在Access上尝试使用完全相同的代码时，它总是说FROM子句有错误.

However when I tried the exact same code on Access, it keeps saying the FROM clause has an error.

我想知道这是否是因为访问语法与SQL Server中的语法有所不同吗?如果有人可以告诉我如何运行此代码，我将非常感谢.

I wonder if this is because the syntax for access is somehow different from that in SQL server? I would really appreciate it if anyone could tell me how to make this code run.

推荐答案

只需使用union all:

SELECT CustomerID, Phone1 as Phone
FROM dbo.CustomerPhones
UNION ALL
SELECT CustomerID, Phone2 as Phone
FROM dbo.CustomerPhones
UNION ALL
SELECT CustomerID, Phone3 as Phone
FROM dbo.CustomerPhones;

如果您要承担删除重复项的开销，请使用UNION.

If you want to incur the overhead of removing duplicates, then use UNION.

这篇关于在Access SQL中取消透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！