在 Access 中将行转换为列 [英] convert rows to columns in Access
问题描述
我在 Stack Overflow 上阅读了许多与我的问题相关的问题，但我认为它们并不能完全解决我的问题.基本上，我下载了一个包含大量数据的 XML 数据集，并将该数据插入到我的 MS Access 数据库中.我想要做的是转换数据，使某些特定的行变成列.
I have read many question on Stack Overflow related to my problem, but I don't think they quite address my problem. Basically I download a XML dataset with lots of data, and inserted that data into my MS Access database. What I want to do is convert the data so that some specific rows become columns.
现在我可能可以在将数据插入数据库之前在代码中手动执行此操作，但这需要大量时间和代码更改，所以我想知道是否可以使用 MS Access 执行此操作.
Now I can probably do this manually in code before inserting the data to database, but that would require lots of time and change in code, so I'm wondering if its possible to do this with MS Access.
这是我的表格的基本外观，以及我想如何转换它.
Here's how my table basically looks, and how I want to convert it.
索引在我的情况下不太相关
The index is not so relevant in my case
[Table1] => [Table1_converted]
[Index] [Name] [Data] [NameID] [NameID] [AA] [BB] [CC] [DD]
1 AA 14 1 1 14 date1 64 61
2 BB(date) 42 1 2 15+19 date2 67+21 63+12
3 CC 64 1 3 9 10
4 DD 61 1 4 date4 1 87
5 AA 15 2
6 BB(date) 35 2
7 CC 67 2
8 DD 63 2
9 AA 9 3
10 CC 10 3
11 AA 19 2
12 BB(date) 20 2
13 CC 21 2
14 DD 12 2
15 BB(date) 83 4
16 CC 1 4
17 DD 87 4
忘了提一下，[Name] 列下的 Values 并不是真正的 AA BB CC.它们比那更复杂.AA 实际上就像01 - NameAA"，没有引号.
Forgot to mention that, the Values under the column [Name] are not really AA BB CC. They are more complex then that. AA is actually like "01 - NameAA", without the quotation mark.
忘记在我的问题中提及一个重要元素，如果 [Name] ex.表中存在具有相同 [NameID] 的 AA，则 [Data] 应将这两个值相加.我已经编辑了表格，在我写过的转换后的表格上.15+19 或 35+20 仅说明对哪些值求和.
Forgot to mention one important element in my question, if the [Name] ex. AA with same [NameID] exists in table, then the [Data] should SUM up those two values. I have edited the tables, on the converted table i have written ex. 15+19 or 35+20 which only illustrates which values are summed up.
再修改一次，希望是最后一次.[Name] BB 之一在 [Data] 中具有 Datetime 类型.NameID 可以是任何一个，没关系.所以我需要一个查询，它在总结时对 [Name] BB 进行异常处理，这样它就不会像对所有其他 [Name] 的 [Data] 那样总结它.对于相同的 [Name] 和 [NameID] 多次写入日期的地方，它总是相同的.
One more edit, hopefully the last. One of the [Name] BB has a Datetime type in [Data]. The NameID can be whichever, does not matter. So i need a query which does an exception on [Name] BB when its summing up, so that it does not sum it up like it does to every other [Name]s [Data]. Places where date is written multiple times for same [Name] and [NameID], it is always the same.
推荐答案
要在 Access 中完成此任务，您需要做的就是
To accomplish this in Access, all you need to do is
TRANSFORM Sum([Data]) AS SumOfData
SELECT [NameID]
FROM [Table1]
GROUP BY [NameID]
PIVOT [Name]
编辑重新:修改后的问题
要以不同方式处理某些 [Name]，我们需要先组合结果(Sum() 等)，然后对结果进行交叉制表
To handle some [Name]s differently we would need to assemble the results (Sum()s, etc.) first, and then crosstab the results
对于[Table1]中的测试数据:
For test data in [Table1]:
Index Name Data NameID
----- ---- ---------- ------
1 AA 14 1
2 BB 2013-12-01 1
3 CC 64 1
4 DD 61 1
5 AA 15 2
6 BB 2013-12-02 2
7 CC 67 2
8 DD 63 2
9 AA 9 3
10 CC 10 3
11 AA 19 2
12 BB 2013-12-02 2
13 CC 21 2
14 DD 12 2
15 BB 2013-12-04 4
16 CC 1 4
17 DD 87 4
查询
TRANSFORM First(columnData) AS whatever
SELECT [NameID]
FROM
(
SELECT [NameID], [Name], Sum([Data]) AS columnData
FROM [Table1]
WHERE [Name] <> 'BB'
GROUP BY [NameID], [Name]
UNION ALL
SELECT DISTINCT [NameID], [Name], [Data]
FROM [Table1]
WHERE [Name] = 'BB'
)
GROUP BY [NameID]
PIVOT [Name]
生产
NameID AA BB CC DD
------ -- ---------- -- --
1 14 2013-12-01 64 61
2 34 2013-12-02 88 75
3 9 10
4 2013-12-04 1 87
这篇关于在 Access 中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
