Geek Answers Handbook

How to simulate UNPIVOT in Access 2010?

UNPIVOT is available in MS SQL-Server 2005, but AFAIK is not in MS Access 2010. How can it be implemented using on-board tools? 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. I'm currently making a turnaround in OLEDB / ATL C ++. Although it's fast enough, I'm still curious. What is the most efficient SQL statement for Access 2010 here?

+7
source share
3 answers

This request ...

 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 ABC key_field -- - - - --------- 1 xyz 3 2 xyz 57 1 xyz 199 2 xyz 234 1 xyz 452 2 xyz 452
+8
source

Unfortunately, there is no easy way to do this on access. You can do this using 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
+1
source

Step 1

You can create a helper table with all column names with values ​​(you can use excel to help you with this: copy the first row of the table to excel> copy paste special> transpose)

Step 2

Create an auto-increase column in your table and index this column

Step 3

Create a new query as a cross-connect, for example

 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;

Do not forget about your work AUX_TABLE

Sorry my english

0
source

More articles:


All Articles