How to Replicate UNPIVOT Functionality in MS Access?

Replicating UNPIVOT in Microsoft Access

Challenge: MS Access 2010 lacks the UNPIVOT function found in SQL Server 2005 and later. This guide demonstrates how to achieve the same result using Access's built-in SQL capabilities.

Scenario:

Imagine this table structure:

The goal is to convert it to a unpivoted format:

Solution:

The UNPIVOT effect can be replicated using a series of UNION ALL statements within an Access SQL query:

<code class="language-sql">SELECT ID, A, B, C, [Key 1] AS Key
FROM tblUnpivotSource
UNION ALL
SELECT ID, A, B, C, [Key 2] AS Key
FROM tblUnpivotSource
UNION ALL
SELECT ID, A, B, C, [Key 3] AS Key
FROM tblUnpivotSource;</code>

Outcome:

Running this query against the sample table produces the desired unpivoted recordset:

The above is the detailed content of How to Replicate UNPIVOT Functionality in MS Access?. For more information, please follow other related articles on the PHP Chinese website!