sql – MS Access中的行的列

发布时间：2020-12-12 06:19:07 所属栏目：MsSql教程来源：网络整理

导读：我试图在MS Access中创建一个查询,最终从此获取输出： Name Cat 1 Cat 2 Cat 3 Cat 4 Cat 5 Cat 6Joe 2 12 10 1 0 0Bob 0 0 0 0 0 0Jody 2 4 3 1 2 0Harry 0 4 14 0 2 0 对于这样的事情： Name Joe Bob Jody HarryCat 1 2 0 2 0Cat 2 12 0 4 4Cat 3 10 0 3 14

我试图在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],"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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!