11

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Andreas Spindler
  • 7,568
  • 4
  • 43
  • 34

3 Answers3

13

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
HansUp
  • 95,961
  • 11
  • 77
  • 135
1

  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.

inetphantom
  • 2,498
  • 4
  • 38
  • 61
Vinicius Sousa
  • 65
  • 7
  • 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. – rohrl77 May 31 '23 at 13:49
1

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
Taryn
  • 242,637
  • 56
  • 362
  • 405