How Can I Achieve UNPIVOT Functionality in Microsoft Access 2010?

Achieving UNPIVOT Functionality in Microsoft Access 2010

Microsoft SQL Server's UNPIVOT function efficiently reshapes data from a wide to a long format. While Access 2010 doesn't offer a direct UNPIVOT equivalent, we can effectively replicate its behavior using SQL.

Imagine a table with multiple attribute columns (like A, B, C) linked to different keys (Key 1, Key 2, Key 3). The objective is to restructure this data so each attribute-key pair occupies its own row.

A UNION ALL query provides a solution:

<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>

This query merges data from each key column into a unified table. Each resulting row signifies an attribute-key pairing:

It's important to note that situations with duplicate key values (such as "452" in the example) will generate multiple rows with identical keys.

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