基于多时间维度查询并转换Excel表结构的SQL及VBA求助

核心思路 #

利用ADODB的SQL动态拼接能力，先提取所有唯一的A值，再针对每个A生成对应的B、C列透视字段，最终通过GROUP BY Time将同一时间的所有A相关列合并到一行。

步骤与代码实现 #

Sub PivotMyTable()
    Dim conn As Object
    Dim rs As Object
    Dim uniqueA As Variant
    Dim sqlFields As String
    Dim fullSql As String
    Dim i As Integer
    
    ' 创建ADODB连接
    Set conn = CreateObject("ADODB.Connection")
    ' 连接字符串（适配xlsx格式，xls格式需改为Excel 8.0）
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    ' 1. 获取所有唯一的A值
    Set rs = conn.Execute("SELECT DISTINCT A FROM myTable")
    uniqueA = rs.GetRows ' 将结果存入二维数组
    rs.Close
    
    ' 2. 动态生成透视列的SQL片段
    sqlFields = ""
    For i = 0 To UBound(uniqueA, 2)
        Dim aVal As String
        aVal = uniqueA(0, i)
        ' 处理A值中的单引号，避免SQL语法错误
        aVal = Replace(aVal, "'", "''")
        ' 为每个A生成对应的B、C列，用IIF+MAX确保同一Time下只取对应A的数值
        sqlFields = sqlFields & ", MAX(IIF(A='" & aVal & "', B, NULL)) AS [" & aVal & "_B]"
        sqlFields = sqlFields & ", MAX(IIF(A='" & aVal & "', C, NULL)) AS [" & aVal & "_C]"
    Next i
    
    ' 3. 拼接完整SQL并执行
    fullSql = "SELECT Time" & sqlFields & " FROM myTable GROUP BY Time ORDER BY Time ASC"
    Set rs = conn.Execute(fullSql)
    
    ' 4. 将结果写入指定工作表（示例为Sheet2，从A1开始）
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")
    ws.Cells.Clear
    
    ' 写入表头
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' 写入数据
    ws.Cells(2, 1).CopyFromRecordset rs
    
    ' 清理资源
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

关键说明 #

• 连接字符串适配：如果是xls格式，需将连接字符串中的Excel 12.0 Xml改为Excel 8.0。
• 聚合函数选择：示例用MAX是假设同一Time+A组合仅一条记录，若存在多条，可根据需求替换为FIRST、SUM或AVG等。
• 原查询失败原因：你之前直接用字符串匹配Time易因时间格式不匹配报错，动态透视SQL直接基于原始表分组，无需手动指定Time值，更可靠高效。

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