Access 2013:透视多列 [英] Access 2013: Pivot multiple columns

问题描述

我有一个奇怪的访问数据库表，我需要在与 Tableau 一起使用之前对其进行透视.我使用的是 Access 2013.数据完全是假的，但结构是正确的.

I have a strange access database table that I need to pivot before using with Tableau. I'm using Access 2013. Data is totally fake but the structure is right.

有 200 个文件，每个文件大约运行 1500 次.我有 50 年的指标.每个文件/运行我有大约 10 个指标.

There are 200 files, with about 1500 runs each. I have 50 years of metrics. I have about 10 metrics per file/run.

+--------------------------------------------------------+
| DataTable                                              |
+-------------+--------------+------+------+------+------+
| FileIDRunID |    Metric    | 1999 | 2000 | 2001 | 2002 |
+-------------+--------------+------+------+------+------+
| 00000100001 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000100001 | Lunch        | 27   | 37   | 50   | 99   |
| 00000100002 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000100002 | Lunch        | 27   | 37   | 50   | 99   |
| 00000200001 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000200001 | Lunch        | 27   | 37   | 50   | 99   |
| 00000200002 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000200002 | Lunch        | 27   | 37   | 50   | 99   |
+-------------+--------------+------+------+------+------+

我想旋转并将指标作为列，将年份作为单独的行.

I would like to pivot and have the metrics as columns and the years as individual rows.

+------------------------------------------------+
| DataTableView                                  |
+-------------+--------------+-----------+-------+
| FileIDRunID |    Year      | Breakfast | Lunch |
+-------------+--------------+-----------+-------+
| 00000100001 | 1999         | 45        | 47    |
| 00000100001 | 2000         | 27        | 37    | 
| 00000100002 | 1999         | 45        | 47    | 
| 00000100002 | 2000         | 27        | 37    |
| 00000200001 | 1999         | 45        | 47    | 
| 00000200001 | 2000         | 27        | 37    | 
| 00000200002 | 1999         | 45        | 47    | 
| 00000200002 | 2000         | 27        | 37    | 
+-------------+--------------+-----------+-------+

我成功地转了一年.

TRANSFORM FIRST(DataTable.[1999])
SELECT FileIDRunID, '1999' as Year
FROM DataTable
GROUP BY FileIDRunID
PIVOT DataTable.Metric

我认为下一步是每年手动联合所有.当我在明年尝试这样做时，虽然出现错误.

I think the next step is to UNION ALL with each year manually. When I try that with the next year though I get an error.

TRANSFORM FIRST(DataTable.[1999])
SELECT FileIDRunID, '1999' as Year
FROM DataTable
GROUP BY FileIDRunID
PIVOT DataTable.Metric
UNION ALL
TRANSFORM FIRST(DataTable.[2000])
SELECT FileIDRunID, '2000' as Year
FROM DataTable
GROUP BY FileIDRunID
PIVOT DataTable.Metric

查询表达式DataTable.Metric"中的语法错误(缺少运算符)联合所有首先转换(数据表.[2000])SELECT FileID, RunID, '2000' 作为年份从数据表GROUP BY FileIDRunIDPIVOT DataTable.Metric'

Syntax error (missing operator) in query expression 'DataTable.Metric UNION ALL TRANSFORM FIRST(DataTable.[2000]) SELECT FileID, RunID, '2000' as Year FROM DataTable GROUP BY FileIDRunID PIVOT DataTable.Metric'

我希望这里有人有更好的主意:).

I'm hoping someone here has a better idea :).

推荐答案

TRANSFORM 可以 只发生在 SQL 语句的开头.

首先取消所有年份的旋转，然后再旋转指标.现在，作为 MS Access 显然在复杂查询(许多联合)方面有其局限性，最好通过一个中间表:

First unpivot all the years and only then pivot the metric. Now, as MS Access apparently has its limits when it comes to complex queries (many unions), it is best to pass via an intermediate table:

SELECT  FileIDRunID, Metric, Year, Value
FROM    (
            SELECT      FileIDRunID, Metric, 1999 As Year, [1999] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2000 As Year, [2000] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2001 As Year, [2001] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2002 As Year, [2002] As Value
            FROM        DataTable
        )
INTO myTempTable;

我已经union-ed 4 年了，但是在MS Access 拒绝声明过于复杂之前，您应该检查一下您能走多远.然后，添加更多的年份，使用相同数量的 union:

I have union-ed 4 years, but you should check how far you can go before MS Access refuses the statement for being too complex. Then, add some more years, with the same number of unions:

INSERT INTO myTempTable (FileIDRunID, Metric, Year, Value)
SELECT  FileIDRunID, Metric, Year, Value
FROM    (
            SELECT      FileIDRunID, Metric, 2003 As Year, [2003] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2004 As Year, [2004] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2005 As Year, [2005] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2006 As Year, [2006] As Value
            FROM        DataTable
        );

等...最终做到这一点:

etc... to finally do this:

TRANSFORM FIRST(Value)
SELECT   FileIDRunID,
         Year
FROM     myTempTable
GROUP BY FileIDRunID,
         Year
PIVOT    Metric

然后删除临时表并压缩数据库以恢复原始大小.如果可能，重新设计数据库以使用临时表中完全非透视的结构.

And then drop the temporary table and compress the database to get the original size back. If possible, redesign the database to use the completely unpivoted structure as in the temporary table.

这篇关于Access 2013:透视多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！