gpt4 book ai didi

sql - MS Access 中的列到行

转载 作者:行者123 更新时间:2023-12-04 21:05:05 27 4
gpt4 key购买 nike

我正在尝试在 MS Access 中创建一个查询以最终从中获取输出:

Name        Cat 1   Cat 2   Cat 3   Cat 4   Cat 5   Cat 6
Joe 2 12 10 1 0 0
Bob 0 0 0 0 0 0
Jody 2 4 3 1 2 0
Harry 0 4 14 0 2 0

像这样:

Name        Joe     Bob     Jody    Harry
Cat 1 2 0 2 0
Cat 2 12 0 4 4
Cat 3 10 0 3 14

这可能吗?

编辑

SELECT [Authorizer Name], 
Sum([Q1A - CD # 1]) AS [Category 1],
Sum([Q2A- CD # 2A] + [Q8A- CD # 2A] + [Q10A- CD # 2A]
+ [CTS A accurate- CD # 2A]
+ [e-correspondence A accurate- CD # 2A]) AS [Category 2],
Sum([Q7A- CD # 2B] + [Q9A- CD # 2B] + [Q11A- CD # 2B]
+ [CTS A procedures- CD # 2B]
+ [e-correspondence A procedures- CD # 2B]) AS [Category 3],
Sum([Q4A- CD # 3]) AS [Category 4],
Sum([Q5A- CD # 4]) AS [Category 5], Sum([Q12A- CD # 5]) AS [Category 6]
FROM [Review Results]
WHERE [Review Results].[Authorizer Name] = 1
GROUP BY [Review Results].[Authorizer Name];

这是表格当前的样子:

Name    X1 X2A   X2B X2C X3A X3B X3C X4 X5 
Joe 1 5 0 1 1 5 6 0 0
Bob 2 7 0 2 1 4 2 1 9
Billy 0 8 0 3 1 3 1 0 9

这是我想要达到的目的:

Name     Joe                  Bob    Billy 
X1 1 2 0
X2 (sum of X2A/X2B/X2C) 6 9 11
X3 (sum of X3A/X3B/X3C) 12 7 5
X4 0 1 0
X5 0 9 9

最佳答案

首先创建一个 UNION 查询来规范化您的表,然后创建一个交叉表来显示数据:

SELECT [Name], "Cat 1" As Cat, [Cat 1] As CatVal FROM Table
UNION ALL
SELECT [Name], "Cat 2" As Cat, [Cat 2] As CatVal FROM Table
<...>

查询向导将指导您完成交叉表的创建。

编辑评论

要规范化样本数据表,您可以说:

SELECT [Name], "X1" As Cat, [X1] As CatVal FROM Table
UNION ALL
SELECT [Name], "X2" As Cat, Nz([X2A],0)+Nz([X2B],0)+Nz([X2C],0) As CatVal
FROM Table
UNION ALL
SELECT [Name], "X3" As Cat, Nz([X3A],0)+Nz([X3B],0)+Nz([X3C],0) As CatVal
FROM Table
UNION ALL
SELECT [Name], "X4" As Cat, [X4] As CatVal FROM Table
UNION ALL
SELECT [Name], "X5" As Cat, [X5] As CatVal FROM Table

假设联合查询是 QueryX:

TRANSFORM First(QueryX.CatVal) AS FirstOfCatVal
SELECT QueryX.Cat
FROM QueryX
GROUP BY QueryX.Cat
PIVOT QueryX.Name;

关于sql - MS Access 中的列到行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9183764/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com