How to simulate UNPIVOT in Access?

Question

UNPIVOT is available in MS SQL-Server 2005, but AFAIK not in MS Access 2010. How can it be implemented with on-board means? For example, I have a table

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

and want to have a table like

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
 2 | x | y | z | 452

Key 452 is a special case. Currently I do the rotation in OLEDB/ATL C++. Although it is fast enough I'm still curious. What is the most efficient SQL statement for Access 2010 here?

Nicotinism

Answer 1

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

... returns this recordset (using your sample table values as tblUnpivotSource) ...

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

Mabel

Answer 2

1. You can create a auxiliary table with all column names as values (can use excel copy the first row of your table to excel > paste special > transpose)

2. Create in your table a auto increment column and index this column

3. Create a new cross join query like the following

SELECT ID, A, B, C
       , AUX_TABLE.KEY_FIELD
       , DLookUp("[" & [AUX_TABLE].[KEY_FIELD] & "]","TABLE","[ID] = " & [TABLE].[ID]) AS KEY_VALUE
FROM TABLE, AUX_TABLE;

Downside would be you have to maintain AUX_TABLE to keep that working. But if this is a one-time-thing this might be the way to go.

Ouphe

as a quick and cheap workaround this is not a bad idea. it could even be automated a bit further, by using the Transpose() Formula, instead of paste special. Then the pivoted table could be linked back into access. would necessitate that the worksheet is opened after each data update. – Commence

good solution, but I don't understand the last argument of the dlookup. What if your original table can't have an autoincrement and you don't have a single primary key ? – Carlenecarleton

Answer 3

Unfortunately there is no easy way to do this with access. You can do this by using a UNION to get each value

SELECT ID, A, B, C, [Key 1] As key
FROM Table
WHERE [Key 1] = 3

UNION ALL

SELECT ID, A, B, C, [Key 1] As key
FROM Table
WHERE [Key 1] = 57

UNION ALL

SELECT ID, A, B, C, [Key 2] As key
FROM Table
WHERE [Key 2] = 199

UNION ALL

SELECT ID, A, B, C, [Key 2] As key
FROM Table
WHERE [Key 2] = 234

UNION ALL

SELECT ID, A, B, C, [Key 3] As key
FROM Table
WHERE [Key 3] = 452

Titanite

So, there's no generic solution? We have to use a data-dependent statement? – Nicotinism

@Andreas there is no quick way to do this with access that is part of its downfall. – Titanite

UNION would remove the not distinct values - why using UNION ALL? – Richardricharda

@Richardricharda If you want DISTINCT, then use UNION. It would depend on your situation and needs. – Titanite