sql查询行转列

昨天下午碰到一个需求,一个大约30万行的表,其中有很多重复行,在这些行中某些字段值是不重复的。

比如有ID,NAME,CONTRACT_id,SALES,PRODUCT等,除了PRODUCT字段,其余字段均是一样的,需要去重并合并PRODUCT字段。

下午头脑发昏,直接写了个O(nm)的vba代码,从16:00开始跑,放到另一台空机器上开始跑,下班的时候看了一下跑了不到1/20,感觉时间浪费在IO上了,当然算法也有很大优化空间,比如排序后再合并。

回来想了想何不直接导入数据库操作,最简单的用access+VBA就行啊。

 

 

Public Function CombStr(TableName As String, FieldName As String, GroupField As String, GroupValue As String) As String
' MsgBox "in module"
    Dim ResultStr As String
    Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset("select " & FieldName & " from " & TableName & " where " & GroupField & "='" & GroupValue & "'")
        If rs.RecordCount > 0 Then
            Do While Not rs.EOF
                If InStr(ResultStr, rs.Fields(0).Value) = 0 Then ResultStr = ResultStr & "," & rs.Fields(0).Value
               
                rs.MoveNext
            Loop
        End If
        If ResultStr <> "" Then ResultStr = Mid(ResultStr, 2)
        CombStr = ResultStr
End Function
SELECT IDCombStr("contract","PRODUCT","ID",ID) AS PRODUCTSUM
FROM contract
GROUP BY ID;

 极速搞定之

 

posted @ 2016-03-24 23:58  迈克儿  阅读(789)  评论(0编辑  收藏  举报