Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to simulate UNPIVOT in Access?

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?

like image 531
Andreas Spindler Avatar asked Aug 31 '11 10:08

Andreas Spindler


People also ask

How do you Unpivot in Microsoft Access?

Select Transform > Unpivot Columns.

What is an option to Unpivot data?

Answer: New columns added during a refresh operation are also unpivoted. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. ... Select the columns you don't want to unpivot. ... Select Transform > Unpivot Other Columns.

What is Pivot vs Unpivot?

PIVOT carries out an aggregation and merges possible multiple rows into a single row in the output. UNPIVOT doesn't reproduce the original table-valued expression result because rows have been merged. Also, null values in the input of UNPIVOT disappear in the output.

What does Unpivot mean?

UNPIVOT is a relational operator that accepts two columns (from a table or subquery), along with a list of columns, and generates a row for each column specified in the list. In a query, it is specified in the FROM clause after the table name or subquery.


1 Answers

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
like image 95
HansUp Avatar answered Oct 21 '22 02:10

HansUp