在ms-access中有group_concat函数吗?

is there a group_concat function in ms-access or something similar?

在ms-access中是否存在group_concat函数?

4 个解决方案

#1

You should ask yourself if you need a generic solution (another is by Allen Browne) or if you need it just for the present purpose. If you really only need it this once, do it the easy way.

你应该问问自己，你是否需要一个通用的解决方案(另一个是Allen Browne的)，或者你是否只是为了当前的目的而需要它。如果你真的只需要这一次，用简单的方法去做。

On a side note, when concatenating lists in VBA code, take advantage of a trick taught to me by long-time Access guru Trevor Best, and that's to stick the delimiter at the beginning of every value and then use Mid() to strip it off. Instead of this inside your loop through the child records:

从一个方面说明,在VBA代码连接列表时,利用技巧长期访问大师特雷福教给我的最好,这是坚持每一个值的分隔符开始,然后使用中期()地带。而不是这里面你遍历子记录:

  If Len(strOutput) = 0 Then
     strOutput = NewValue
  Else
     strOutput = strOutput & ", " & NewValue
  End If

...use this inside the loop:

…在循环中使用这个:

  strOutput = strOutput & ", " & NewValue

...and then when you exit the loop, strip off the leading delimiter:

…当你退出循环时，去掉前导分隔符:

  strOutput = Mid(strOutput, 3)

This has implications all over the place and simplifies code for concatenation in a whole host of contexts.

这就产生了各种各样的影响，并简化了在整个上下文环境中进行连接的代码。

#2

I found this post by Duane Hookum (a Microsoft MVP) that claims to be able to do what you want. I have not tested it though.

我发现了这篇由Duane Hookum(微软MVP)写的文章，它宣称能够做你想做的事。我还没有测试过。

By the way, in case you are interested, this is how I found it:

顺便说一下，如果你有兴趣的话，我是这样发现的:

First search: group_concat access lead me to this post with this answer but the link was broken.

第一个搜索:group_concat访问引导我找到这个答案，但是链接被破坏了。

Then I searched again after the content that the answer was attempting to link to, and found it: site:http://www.rogersaccesslibrary.com/ concatenate.

然后我又搜索了答案试图链接到的内容，并找到了它:站点:http://www.rogersaccesslibrary.com/ concatenate。

#3

There's an access function to group multiple values into one value (a custom aggregate, I guess.) The link is http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'

有一个访问函数将多个值分组为一个值(我猜是自定义聚合)。链接是http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName=的通用% 20函数% 20 % 20连接% 20孩子% 20记录”

but the site is down for now. If you google the href, you'll find lots of referneces and examples.

但网站暂时关闭。如果你在href上谷歌，你会发现很多引用和例子。

#4

No. Access does not have a GROUP_CONCAT function. However, it is possible to create a VBA function which will let you pass a string containing a SQL statement and get the equivalent functionality (not that I'd recommend it but it is possible).

不。访问没有GROUP_CONCAT函数。但是，可以创建一个VBA函数，它将允许您传递一个包含SQL语句的字符串并获得等效的功能(我不建议这样做，但这是可能的)。

Taking my own personal wayback machine, here is some code I wrote back when dinosaurs ruled the Earth:

用我自己的个人回程机，这里有一些我在恐龙统治地球时写下的代码:

Public Function ListQuery(SQL As String _
                            , Optional ColumnDelimiter As String = " " _
                            , Optional RowDelimter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
'   1. SQL is a valid Select statement
'   2. ColumnDelimiter is the character(s) that separate each column
'   3. RowDelimiter is the character(s) that separate each row
'RETURN VAL:
'DESIGN NOTES:

Const PROCNAME = "ListQuery"
Const MAXROWS = 100
Const MAXCOLS = 10
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oField As ADODB.Field
Dim sRow As cString
Dim sResult As cString

On Error GoTo ProcErr

Set sResult = New cString
Set sRow = New cString
Set oConn = GetADOConn()

sResult.Clear
Do Until oRS.EOF
    sRow.Clear

    For Each oField In oRS.Fields
        With sRow
            If .Length > 0 Then
                .Append ColumnDelimiter
            End If

            .Append Nz(oField.Value)
        End With
    Next oField

    sRow.Trim
    If sRow.Length > 0 Then
        With sResult
            .Append sRow
            .Append RowDelimter
        End With
    End If

    oRS.MoveNext
Loop
oRS.Close
oConn.Close

With sResult
    If .Right(Len(RowDelimter)).Value = RowDelimter Then
        .Length = .Length - Len(RowDelimter)
    End If
End With

FunctionResult:
    ListQuery = sResult.Value

CleanUp:
    Set sResult = Nothing
    Set sRow = Nothing
    Set oField = Nothing
    Set oRS = Nothing
    Set oConn = Nothing

Exit Function
ProcErr:
    ' logging  code...
    Resume CleanUp

End Function

The GetADOConn function is a centralized function to retrieve the current database connection. cString is a class that mimics the behavior of .NET's StringBuilder class but was written long before .NET was anything other than a TLD and marketing hype. Since this is getting called on every row, VBA's built-in string concatenation will be slow and thus something like a StringBuilder class is needed. The original code (which I've partially modified) had a cap on the number of rows and columns that could be used which is what the constants are all about.

GetADOConn函数是一个用于检索当前数据库连接的集中函数。cString是一个类，它模仿。net的StringBuilder类的行为，但早在。net之前就已经写好了，而不是TLD和市场宣传。由于在每一行上都要调用它，所以VBA的内置字符串连接将会很慢，因此需要类似StringBuilder类的东西。原始代码(我已经部分修改过)对可以使用的行数和列数有一个上限，这就是常量的含义。

#1