重新组织Access表(将行转换为列) [英] Re-organizing Access table (convert rows to columns)

问题描述

我有一个数据库，其中有两个相似的字段(与另一个表匹配)，然后第三个是唯一的.我想将表转换为在列而不是单独的记录中列出的第3个唯一值.

I have a database where there are 2 fields similar (that match another table) and then the third is unique. I want to convert the table to have the 3rd unique value listed in columns instead of a separate record.

I.E.

5/1/2013  A321  1  $1,120
5/2/2013  A325  1  $2,261
5/1/2013  A321  2  $2,120

将转换为

5/1/2013  A321  $1,120  $2,120
5/2/2013  A325  $2,261

我可以为第3列中的每个值创建单独的查询，然后使用另一个查询以我想要的方式将它们联接起来，但我希望有一个更优雅的解决方案.

I could create separate queries for each value in column 3 and then have another query that joins them in the fashion I want them to but I was hoping there was a more elegant solution.

推荐答案

用于[OldTable]中的测试数据

For test data in [OldTable]

FieldA      FieldB  FieldC  FieldD
----------  ------  ------  ------
2013-05-01  A321         1    1120
2013-05-02  A325         1    2261
2013-05-01  A321         2    2120

交叉表查询

TRANSFORM First(FieldD) AS FirstOfFieldD
SELECT FieldA, FieldB
FROM 
    (
        SELECT
            FieldA,
            FieldB,
            'Value' & FieldC AS ColumnName,
            FieldD
        FROM OldTable
    )
GROUP BY FieldA, FieldB
PIVOT ColumnName

返回

FieldA      FieldB  Value1  Value2
----------  ------  ------  ------
2013-05-01  A321      1120    2120
2013-05-02  A325      2261        

如果您想实际创建一个包含这些结果的新表，则将上述查询保存为[xtabQuery]在Access中，然后运行

If you want to actually create a new table containing those results then save the above query in Access as [xtabQuery] and then run

SELECT xtabQuery.* INTO NewTable
FROM xtabQuery;

这篇关于重新组织Access表(将行转换为列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！