SQL - Access-VBA自合并表数据

Question

我有一个非常杂乱无章的桌子从一个遗留系统。看起来是这样的：

ID  | ProjArea | Task | SumDays | AI | SDLC | DEV | PT | CS | ENG |
-------------------------------------------------------------------
239 |    A     | SDLC |   2     |    |   2  |     |    |    |     |   
239 |    A     | DEV  |   3     |    |      |  3  |    |    |     | 
239 |    A     | AI   |   8     | 8  |      |     |    |    |     | 
239 |    A     | PT   |   2     |    |      |     | 2  |    |     | 
239 |    B     | DEV  |   4     |    |      |   4 |    |    |     | 
239 |    B     | AI   |   2     | 2  |      |     |    |    |     | 
239 |    B     | PT   |   2     |    |      |     | 2  |    |     | 
71  |    B     | DEV  |   5     |    |      |   5 |    |    |     | 
71  |    B     | AI   |   2     | 2  |      |     |    |    |     | 
71  |    A     | PT   |   2     |    |      |     | 2  |    |     | 

从上表中可以看出，将任务作为列和任务列是非常冗余的。我正在设计一个查询，它将数据合并到一个列中，而不是每个项目区域的多列数据。

预期的最终结果如下：

ID  | ProjArea | SumDays | AI | SDLC | DEV | PT | CS | ENG |
-------------------------------------------------------------------
239 |    A     |   15    | 8  |   2  |  3  | 2  |    |     |   
239 |    B     |    8    | 2  |      |  4  | 2  |    |     | 
71  |    A     |    2    |    |      |     | 2  |    |     | 
71  |    B     |    7    | 2  |      |  5  |    |    |     | 

我已经在桌面上尝试了一个完全的外部加入，但仍然未能做到这一点。对于如何实现这一点，有什么想法或建议吗？我正在编写VBA代码来尝试这一点。

Answer 1

我认为您可以通过聚合查询获得所需的内容：

select id, projarea, sum(sumdays) as sumdays,
       sum(ai) as ai, sum(sdlc) as sdlc, sum(dev) as dev, sum(pt) as pt,
       sum(cs) as cs, sum(eng) as eng
from t
group by id, projarea;