Search code examples
sqlms-accessunpivot

Unpivot in Access SQL

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;

from this webpage: https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/

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

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.

Solution

  • Just use 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;

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