How can you collapse values to a single row in a SQL Statement?

Question

Assume you have a table with two columns "Name" and "State".

Names can repeat in the table with different states. For example:

Name/State
Bob/NY
Bob/PA
Bob/VA

I want to write a SQL statement (without the use of cursors) that will return "Bob NY, PA, VA" on a single line.

Does anyone know of a way to do this in a straight SQL Statement?

Alternatively, any good tricks for accomplishing this using the functionality of Microsoft Access?

Answer 1

MySQL has GROUP_CONCAT, in MSSQL you can write a UDF that does some neat tricks.

In Access, you'd probably have to use VBA. The function that follows is a generic, and as such, suffers from a complete lack of optimization. If you're writing it to fill a special case, you can use static variables and collections to only have to look up data once. This function will make a request of the database for every row in the query (and sometimes more often--Access often runs code functions multiple times per row). It also assumes that the data coming in and the data coming out is textual.

'----------------
Function Group_Concat(Table As String, KeyField As String, KeyValue As Variant, ValueField As String, Optional Separator As String = ", ") As String
Dim sSQL As String
Dim rs As New ADODB.Recordset
Dim sResults() As String, iResultPosition As Integer

sSQL = "SELECT [" & ValueField & "] FROM [" & Table & "] WHERE [" & KeyField & "] "
If IsNull(KeyValue) Then
sSQL = sSQL & " Is Null"
Else
' Make sure we don't accidentally feed in SQL-breaking (or possibly SQL-injecting) quotes
' This makes it so a single quote character sent in is turned into a single-character wildcard
sSQL = sSQL & " Like '" & Replace(KeyValue, "'", "_") & "'"
End If

' Don't return Null values
sSQL = sSQL & " AND [" & ValueField & "] Is Not Null"

' Assume you want the data sorted
sSQL = sSQL & " ORDER BY [" & ValueField & "]"

' Nod toward optimization: By using a Static (or Keyset) recordset, we can find out how many rows
' are being returned, and use an array to store the contents of the row. VB bogs down heavily
' under string concatenation. However, if you're only expecting 3 or 4 results per request
' the array may be more overhead.

On Error Resume Next
rs.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If Err.Number <> 0 Then
' Catch errors and return them as the result
Group_Concat = "#ERROR#: (" & CStr(Err.Number) & ") " & Err.Description
Exit Function
End If
On Error GoTo 0

If rs.RecordCount = 0 Then
rs.Close
Exit Function
End If

ReDim sResults(0 To rs.RecordCount - 1)
Do Until rs.EOF
sResults(iResultPosition) = rs.Fields(ValueField).Value

iResultPosition = iResultPosition + 1
rs.MoveNext
Loop
rs.Close

Group_Concat = Join(sResults, Separator)
End Function
'-----------------

Sample SQL, assuming a table called tblNamesStates with two columns: Name, and State

SELECT
Name
, Group_Concat( "tblNamesStates", "Name", [Name], "State" )
FROM tblNamesStates
GROUP BY Name

Hope that helps.

Answer 2

I know there is a CONCAT function in MySQL, but not sure if this works in Access

Answer 3

To do it in mysql you would use:
SELECT name, GROUP_CONCAT(state) FROM table GROUP BY name

my mistake the command was GROUP_CONCAT