使用VBA Excel的SQL Pivot函数文本文件数据库 [英] SQL pivot function text file database with VBA Excel

问题描述

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

We do not have access to SQL server at work so I have to design an app within Excel VBA and using a text file (CSV) to store the data.

我可以很方便地查询数据(加入CSV数据)，但是我想使用SQL Pivot/Unpivot语句将其中一列转置为行.我不确定是否存在该功能，因为我不断收到错误消息，提示我的FROM子句中存在语法错误.

I have no problem querying data, joining the CSV's, but I would like to use the SQL Pivot/Unpivot statement to transpose one of the columns into rows. I'm not sure if that functionality exists as I keep getting the error that there's a syntax error in my FROM clause.

    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

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

这很简单，可以处理数据透视表，但是我希望用户能够在数据上键入内容.

This is simple enough to do with a pivot table but I wanted the user to be able to type over the data.

此外，是否还有使其动态的方法，因为可能的客户端数量未知，因此需要扩展行数.

Additionally, is there anyway to make it dynamic because there will be an unknown number of possible clients so the number of rows would need to expand.

预先感谢

推荐答案

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

结束功能

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

Transform ended up doing what I needed.

感谢您的帮助！

这篇关于使用VBA Excel的SQL Pivot函数文本文件数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！