sql - 如何编写 SQL 语句将多条记录扁平化为一条记录？

Question

我有一个包含多个按字段分组的表，如第一个表所示。我什至在努力启动查询语句以使其看起来像第二个表。非常感谢您的帮助！

GRP      SET       Start     Completed
A         1       3/1/2019
A         2       3/2/2019   3/5/2019
B         1       4/1/2019   4/2/2019
B         2       2/13/2019  2/27/2019

期望：

GRP      SET1_Start    SET1_Comp   SET2_Start   SET2_Comp
A         3/1/2019                  3/2/2019     3/5/2019
B         4/1/2019     4/2/2019     2/13/2019    2/27/2019

预先感谢您的帮助！！！

标签： sqlms-access

Answer 1

使用iif()在 MS Access 中使用的条件聚合：

select grp,
       max(iif(set = 1, start, null)) as set1_start,
       max(iif(set = 1, completed, null)) as set1_completed,
       max(iif(set = 2, start, null)) as set2_start,
       max(iif(set = 2, completed, null)) as set2_completed
from t
group by grp;