如何在 Access 中模拟 UNPIVOT?
UNPIVOT 在 MS SQL-Server 2005 中可用，但 AFAIK 在 MS Access 2010 中不可用.它如何通过机载方式实现?例如，我有一张桌子
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
想要一张像
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
密钥 452 是一个特例.目前我在 OLEDB/ATL C++ 中进行旋转.虽然它足够快，但我仍然很好奇.此处 Access 2010 最有效的 SQL 语句是什么?
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?
推荐答案
这个查询...
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;
... 返回此记录集(使用您的示例表值作为 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
相关文章