MS Access中创建去重逗号分隔拼接公司列的查询实现问题

前置说明 #

你现有两张表结构如下：

• 食品生产商表（表名：食品及对应关联生产商表）：字段FoodID、Food、Company
• 客户食用记录表（表名：客户食品食用情况记录表）：字段ClientID、ClientName、Apple、Orange、Banana

实现步骤 #

步骤1：创建逆透视中间查询，将宽表转为长表 #

由于你的客户食用记录是按食品作为列存储的，首先需要将其转为「1个客户+1个食用食品」的行式结构，保存查询名为qry_ClientFoodUnpivot，SQL代码如下：

SELECT ClientID, ClientName, 'Apple' AS Food FROM 客户食品食用情况记录表 WHERE Apple = 'Yes'
UNION ALL
SELECT ClientID, ClientName, 'Orange' AS Food FROM 客户食品食用情况记录表 WHERE Orange = 'Yes'
UNION ALL
SELECT ClientID, ClientName, 'Banana' AS Food FROM 客户食品食用情况记录表 WHERE Banana = 'Yes'

步骤2：创建去重关联中间查询 #

将逆透视结果和食品生产商表关联，并且按客户+生产商去重，避免同一生产商多次计入，保存查询名为qry_ClientCompanyDistinct，SQL代码如下：

SELECT DISTINCT u.ClientID, u.ClientName, f.Company
FROM qry_ClientFoodUnpivot u
INNER JOIN 食品及对应关联生产商表 f ON u.Food = f.Food

步骤3：编写自定义拼接函数 #

Access没有内置分组拼接字符串的函数，需要手动创建VBA函数实现：

1. 按Alt+F11打开VBA编辑器，右键你的数据库名→插入→模块
2. 在模块中粘贴如下代码，保存模块：

Public Function GetAssociatedCompanies(ClientID As Long) As String
    Dim rs As DAO.Recordset
    Dim res As String
    Set rs = CurrentDb.OpenRecordset("SELECT Company FROM qry_ClientCompanyDistinct WHERE ClientID = " & ClientID)
    Do While Not rs.EOF
        If res <> "" Then res = res & ", "
        res = res & rs!Company
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    GetAssociatedCompanies = res
End Function

步骤4：编写最终查询 #

执行以下SQL即可得到预期结果：

SELECT 
  ClientID,
  ClientName,
  GetAssociatedCompanies(ClientID) AS AssociatedCompanies
FROM 客户食品食用情况记录表
ORDER BY ClientID

结果验证 #

运行后返回的结果和你预期完全一致：

• ClientID=1 Bob：Vino Farms
• ClientID=2 Tyler：Vino Farms, Citrus Co.
• ClientID=3 Joe：空值

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