How to Simulate UNPIVOT in Microsoft Access 2010?

Emulating UNPIVOT in Access 2010

MS SQL Server 2005 supports the UNPIVOT function, but Access 2010 does not. How to use Access's own functions to achieve similar effects? For example, let's say we have a table:

<code><h2 id="ID-A-B-C-Key-Key-Key">ID | A | B | C | Key 1 | Key 2 | Key 3</h2>
<p>1 | x | y | z |     3 |   199 |   452<br>2 | x | y | z |    57 |   234 |   452<br></p></code>

We want to convert this to:

<code><h2 id="ID-A-B-C-Key">ID | A | B | C | Key</h2>
<p>1 | x | y | z |   3<br>2 | x | y | z |  57<br>1 | x | y | z | 199<br>2 | x | y | z | 234<br>2 | x | y | z | 452<br></p></code>

Where Key 452 is a repeated value. Currently I'm converting in OLEDB/ATL C. While it's fast enough, I'm still curious how to do it more efficiently in Access 2010. What is the most efficient SQL statement?

Solution:

You can use the following SQL query:

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;

...After executing this query (assuming your example table is named tblUnpivotSource), you will get the following result set:

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

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