SQL: How can I concatenate grouped values?

Question

If I have the following table (shown in the image below), how can I write SQL code that would concatenate the grouped results?

For this example, I'd want to group by the LetterColumn and concatenate the NumberColumn

So the desired results would be:

Note: Same question has been posted not for SQL but for Power Query here: PowerQuery: How can I concatenate grouped values?

You need to write a UDF to do this or to write the code in VBA. — Gordon Linoff, Apr 03 '20 at 10:33

score 1 · Accepted Answer · answered Apr 03 '20 at 10:54

Below is a small VBA procedure that does the grouping of the number column for a given letter (without the quotes, which would be trivial to add):

Public Function fGroupColumn(strLetter As String) As String
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim strSQL As String
    Set db = DBEngine(0)(0)
    strSQL = "SELECT NumberColumn FROM tblColumn WHERE LetterColumn='" & strLetter & "' ORDER BY NumberColumn ASC;"
    Set rsData = db.OpenRecordset(strSQL)
    If Not (rsData.BOF And rsData.EOF) Then
        Do
            fGroupColumn = fGroupColumn & rsData!NumberColumn & ","
            rsData.MoveNext
        Loop Until rsData.EOF
    End If
    If Right(fGroupColumn, 1) = "," Then fGroupColumn = Left(fGroupColumn, Len(fGroupColumn) - 1)
fExit:
    On Error Resume Next
    rsData.Close
    Set rsData = Nothing
    Set db = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fGroupColumn", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

You can then use this function within an Access query to get the result that you want.

Regards,

SQL: How can I concatenate grouped values?

1 Answers1