How can you collapse values to a single row in a SQL Statement?
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?
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.
June 30th, 2009 at 2:44 am
I know there is a CONCAT function in MySQL, but not sure if this works in Access
References :
June 30th, 2009 at 3:15 am
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
References :
June 30th, 2009 at 3:43 am
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.
References :