Access数据库中如何将同表关联的套装组件分列展示？

现有表结构 #

TbProduct表（产品主表） #

productId  |  descriptionProduct
   1       |     Cable         
   2       |     Mouse         
   3       |     Keyboard        
   4       |     Screen
   5       |     Set1       
   6       |     Set2        
   7       |     Set3  

TbCompose表（套装组件关联表） #

productIdComposed  |  productIdComposing
   5               |     1         
   5               |     2         
   6               |     1       
   6               |     4
   7               |     3       
   7               |     4        

TbCompose的两个字段均为外键，与TbProduct的productId字段建立一对多关联：productIdComposed对应套装ID，productIdComposing对应套装包含的组件ID。需求是将每套固定2个组件的套装，把组件拆分为ProductComp1、ProductComp2两个字段，输出如下格式：

descriptionProduct |  ProductComp1   | ProductComp2
   Set1            |     Cable       |     Mouse
   Set2            |     Cable       |     Screen
   Set3            |     Keyboard    |     Screen

补充说明：每套固定包含2个组件，套装需保留在TbProduct表中，不能拆分到其他表。

实现查询语句 #

方法1：基于排名的行转列 #

通过子查询给每个套装的组件分配序号，再利用聚合函数实现列转行：

SELECT
    p.descriptionProduct,
    MAX(IIF(c.rank = 1, pc.descriptionProduct, NULL)) AS ProductComp1,
    MAX(IIF(c.rank = 2, pc.descriptionProduct, NULL)) AS ProductComp2
FROM
    TbProduct p
INNER JOIN (
    SELECT
        productIdComposed,
        productIdComposing,
        (SELECT COUNT(*) 
         FROM TbCompose c2 
         WHERE c2.productIdComposed = c1.productIdComposed 
           AND c2.productIdComposing <= c1.productIdComposing) AS rank
    FROM TbCompose c1
) c ON p.productId = c.productIdComposed
INNER JOIN TbProduct pc ON c.productIdComposing = pc.productId
WHERE
    p.descriptionProduct LIKE 'Set%'
GROUP BY
    p.descriptionProduct
ORDER BY
    p.descriptionProduct;

方法2：自连接实现配对 #

利用套装固定2个组件的特性，通过自连接关联两个组件，同时避免重复组合：

SELECT
    p.descriptionProduct,
    pc1.descriptionProduct AS ProductComp1,
    pc2.descriptionProduct AS ProductComp2
FROM
    TbProduct p
INNER JOIN TbCompose c1 ON p.productId = c1.productIdComposed
INNER JOIN TbProduct pc1 ON c1.productIdComposing = pc1.productId
INNER JOIN TbCompose c2 ON p.productId = c2.productIdComposed
INNER JOIN TbProduct pc2 ON c2.productIdComposing = pc2.productId
WHERE
    p.descriptionProduct LIKE 'Set%'
    AND c1.productIdComposing < c2.productIdComposing
GROUP BY
    p.descriptionProduct, pc1.descriptionProduct, pc2.descriptionProduct;

两个方法均可输出目标格式，可根据实际数据情况选择使用。

内容的提问来源于stack exchange，提问作者Sorbet