UNPIVOT
is available in MS SQL-Server 2005,开发者_StackOverflow 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?
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
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
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)
Create in your table a auto increment column and index this column
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.
精彩评论