使用VBA Excel的SQL Pivot函数文本文件数据库

debugcn 发表于 Dev

Changster

我们无法在工作中访问SQL Server，因此我必须在Excel VBA中设计一个应用程序，并使用文本文件（CSV）存储数据。

我在查询数据，加入CSV时没有问题，但是我想使用SQL Pivot / Unpivot语句将其中一列转置为行。我不确定是否存在该功能，因为我不断收到错误消息，提示我的FROM子句中存在语法错误。

    Public Function getData() As ADODB.Recordset

    Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset
    path = ThisWorkbook.path & "\"

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & path & ";" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")

    rs.ActiveConnection = conn
    rs.Source = "SELECT * " & _
                "FROM " & _
                    "(SELECT emp_id, client, allocation " & _
                    "FROM ALLOCATIONdb.csv) AS s " & _
                "PIVOT (SUM(allocation) FOR client IN (client1, client2)) AS pvt"

    Set getData = rs

End Function

我的数据当前看起来像图片的顶部，而我希望它看起来像底部。

这对于数据透视表来说很简单，但是我希望用户能够在数据上键入内容。

此外，还是有办法使它动态化，因为可能会有未知数量的客户端，因此需要扩展行数。

提前致谢

Changster

Public Function getData(db1 As String, db2 As String, var1 As String, var2 As String) As ADODB.Recordset

Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset
path = ThisWorkbook.path & "\"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & path & ";" & _
           "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")

rs.ActiveConnection = conn

rs.Source = "TRANSFORM sum(a.allocation) " & _
            "SELECT a.emp_id, b.name, b.[new title], b.[new department] " & _
            "FROM " & db1 & " a " & _
            "INNER JOIN " & db2 & " b " & _
            "ON a.emp_id = b.emp " & _
            "GROUP BY a.emp_id, b.name, b.[new title], b.[new department] " & _
            "ORDER BY b.[new department], b.[new title], b.name " & _
            "PIVOT a.client"

Set getData = rs

结束功能

转换最终完成了我所需要的。

谢谢您的帮助！

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。