Access VBA SQL多列分组透视：如何保留其他属性列？

这个需求其实用条件聚合就能轻松解决，不需要复杂的透视操作——本质上就是把Desc列里的Sales和Costs转成独立列，同时保留Report Date、Name、Location这些分组属性，让每个唯一的属性组合对应一行数据。

核心SQL逻辑 #

先看实现转换的核心SQL语句，原理是通过GROUP BY把需要保留的属性列作为分组依据，再用IIF函数结合SUM实现条件求和，把对应Desc的值映射到新列：

SELECT 
    [report date] AS [Report Date],
    [Name],
    [Location],
    SUM(IIF([Desc] = 'Sales', [Value], 0)) AS [Sales],
    SUM(IIF([Desc] = 'Costs', [Value], 0)) AS [Costs]
INTO PivotedSalesData  -- 生成的新表名，可自行修改
FROM YourOriginalTableName  -- 替换成你的原表名称
GROUP BY [report date], [Name], [Location];

• GROUP BY [report date], [Name], [Location]：确保这三列的每个唯一组合都会生成一行结果，所有需要保留的属性列必须全部加入分组子句
• SUM(IIF([Desc] = 'Sales', [Value], 0))：当Desc是Sales时取对应Value，否则取0，求和后得到该分组的总销售额，同理处理Costs

整合到Access VBA代码 #

把上面的SQL放到VBA中执行，完整的创建新表代码示例：

Sub CreatePivotedTable()
    Dim strSQL As String
    Dim originalTableName As String
    Dim newTableName As String
    
    ' 替换成你的实际表名
    originalTableName = "YourOriginalTableName"
    newTableName = "PivotedSalesData"
    
    ' 构建SQL语句
    strSQL = "SELECT " & _
             "[report date] AS [Report Date], " & _
             "[Name], " & _
             "[Location], " & _
             "SUM(IIF([Desc] = 'Sales', [Value], 0)) AS [Sales], " & _
             "SUM(IIF([Desc] = 'Costs', [Value], 0)) AS [Costs] " & _
             "INTO " & newTableName & " " & _
             "FROM " & originalTableName & " " & _
             "GROUP BY [report date], [Name], [Location];"
    
    On Error GoTo ErrorHandler
    ' 执行SQL创建新表
    CurrentDb.Execute strSQL, dbFailOnError
    MsgBox "透视表创建成功！新表名称：" & newTableName, vbInformation
    
ExitSub:
    Exit Sub
ErrorHandler:
    MsgBox "创建失败：" & Err.Description, vbCritical
    Resume ExitSub
End Sub

补充说明 #

1. 如果Value列不是数值类型，可以根据需求把SUM换成First或Max等聚合函数
2. 如果原表中有更多需要转换的Desc值，只需继续添加SUM(IIF(...))格式的语句即可

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