如何在 Access 中獲得等效的數組或字元串 GROUP BY 聚合函式？

在SQL 聚合函式 (Office 2007)GROUP BY中，Access SQL中只有這些聚合函式：

Count(), Sum(), Avg(), 
First(), Last(), Min(), Max(), 
StDev(), StDevP(), Var(), VarP()

在PostgreSQL 聚合函式中，PostgreSQL中還有（除其他外）這些聚合函式：

array_agg (expression)
 -- input values, including nulls, concatenated into an array

string_agg (expression, delimiter)
 -- input values concatenated into a string, separated by delimiter

如何GROUP BY在 Access 中獲得等效的數組或字元串聚合函式？是否可以建構Access SQL 聚合函式？如果不清楚，如果我這個數據

ID col
-----
1  A
1  B
1  C
2  A
3  A
3  B

如何獲得以下聚合？

ID cols
----------
1  A, B, C
2  A
3  A, B

我必須求助於VBA嗎？還有其他想法嗎？

我使用的是 Access 2007 - 2010，但如果新版本有所不同，請告訴我。

Access 確實帶有一組域聚合功能。可以在此處或在此處的 MSN 文件中找到這些內容的概述。不幸的是，沒有本地域連接功能。

幸運的是，Patrick Matthews 在這裡發布了一個域連接函式（並在下面複製以防止連結失效），它將完成您正在尋找的內容。

從 DConcat() 的文件中：

SELECT DConcat("Account","Sample") AS Accounts
FROM Sample
GROUP BY DConcat("Account","Sample");


Returns:
Accounts
-----------------------------------------------------------------------------
Acct1, Acct10, Acct2, Acct3, Acct4, Acct5, Acct6, Acct7, Acct8, Acct9

對於有問題的具體範例：

SELECT ID, DConcat("col","YourTable", "ID=1") AS cols
FROM YourTable
WHERE ID=1
GROUP BY ID, DConcat("col","YourTable", "ID=1")

應該返回這些值：

ID cols
----------
1  A, B, C

然後對每個不同的所需ID值重複。

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
   Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
   Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
   Optional Limit As Long = 0)

   ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
   ' incarnations over the years

   ' Requires reference to Microsoft DAO library

   ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
   ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
   '
   '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
   '    FROM SomeTable
   '    GROUP BY Field1
   '
   ' will return the distinct values of Field1, along with a concatenated list of all the
   ' distinct Field2 values associated with each Field1 value.

   ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
   '   one column, but the function accommodates multiple).  Place field names in square
   '   brackets if they do not meet the customary rules for naming DB objects
   ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
   '   if they do not meet the customary rules for naming DB objects
   ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
   '   or Or as needed to build the right logic, and to encase text values in single quotes
   '   and dates in #
   ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
   '   Delimiter1 is applied to each row in the code query's result set
   ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
   '   set if ConcatColumns specifies more than one column (default is ", ")
   ' Distinct (optional) determines whether the distinct values are concatenated (True,
   '   default), or whether all values are concatenated (and thus may get repeated)
   ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
   '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
   '   column gets sorted
   ' Limit (optional) places a limit on how many items are placed into the concatenated string.
   '   The Limit argument works as a TOP N qualifier in the SELECT clause

   Dim rs As DAO.Recordset
   Dim SQL As String
   Dim ThisItem As String
   Dim FieldCounter As Long

   On Error GoTo ErrHandler

   ' Initialize to Null

   DConcat = Null

   ' Build up a query to grab the information needed for the concatenation

   SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
           IIf(Limit > 0, "TOP " & Limit & " ", "") & _
           ConcatColumns & " " & _
       "FROM " & Tbl & " " & _
       IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
       Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
           Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")

   ' Open the recordset and loop through it:
   ' 1) Concatenate each column in each row of the recordset
   ' 2) Concatenate the resulting concatenated rows in the function's return value

   Set rs = CurrentDb.OpenRecordset(SQL)
   With rs
       Do Until .EOF

           ' Initialize variable for this row

           ThisItem = ""

           ' Concatenate columns on this row

           For FieldCounter = 0 To rs.Fields.Count - 1
               ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
           Next

           ' Trim leading delimiter

           ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

           ' Concatenate row result to function return value

           DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
           .MoveNext
       Loop
       .Close
   End With

   ' Trim leading delimiter

   If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)

   GoTo Cleanup

ErrHandler:

   ' Error is most likely an invalid database object name, or bad syntax in the Criteria

   DConcat = CVErr(Err.Number)

Cleanup:
   Set rs = Nothing

End Function

引用自：https://dba.stackexchange.com/questions/98089