How Can I Simulate UNPIVOT Functionality in Microsoft Access?

Replicating UNPIVOT Functionality within Microsoft Access

Microsoft SQL Server's UNPIVOT function efficiently transforms wide-format tables into a flatter structure. While this functionality isn't directly available in Access 2010, we can achieve the same result using a UNION ALL query.

Here's how to effectively simulate UNPIVOT in Access:

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

Let's illustrate with an example. Consider this sample table:

<code>ID | A | B | C | Key 1 | Key 2 | Key 3
---------------------------------------
 1 | x | y | z |     3 |   199 |   452
 2 | x | y | z |    57 |   234 |   452</code>

Executing the above query on this table yields:

<code>ID | A | B | C | key_field
-- - - - ---------
 1 | x | y | z |         3
 2 | x | y | z |        57
 1 | x | y | z |       199
 2 | x | y | z |       234
 1 | x | y | z |       452
 2 | x | y | z |       452</code>

The UNION ALL approach successfully "unpivots" the data, converting the table into a flat representation. This method offers a practical and adaptable workaround for Access users needing UNPIVOT capabilities.

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