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:

ID	A	B	C	Key 1	Key 2	Key 3
1	x	y	z	3	199	452
2	x	y	z	57	234	452

The goal is to convert it to a unpivoted format:

ID	A	B	C	Key
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

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:

ID	A	B	C	Key
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

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!