Combining multiple rows into one row in SQL

[duluter]

Hi.

My testTable table has two fields (words and current) and looks like this:

words______current
ten_________no
this________yes
half________yes
question____yes
fibber_______no


I want to query the table to find all the words that are "current", like this:

words___
this
half
question


But instead of a result set that has multiple rows, I want all the rows combined into one row, comma separated, like this:

words___________
this, half, question



I dug around and found a post elsewhere that indicated that this would do the trick:

DECLARE @WordList varchar(500)
SELECT @WordList = COALESCE(@WordList + ', ', '') + testTable.words
FROM testTable
WHERE testTable.current = 1
SELECT @WordList


I don't really understand this much. When I try to run it in Access, I get this error:

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

And when I click OK, Access highlights the first line of the SQL (my DECLARE statement).


Can anyone help me out with this?


Thanks,

Duluter

 

[boblarson]

The problem is that you are trying to use a SQL Stored Procedure in Access. Are you connected to a SQL Server in any way? Are you using an ADP? If not, this SQL Statement will only work in SQL Server as a Stored Procedure.

 

[duluter]

I do not have access to SQL Server--at least that I know of. Assuming a pure Access implementation, how would you suggest I go about it?

Duluter

 

[boblarson]

Create a function to do the same thing and call it

 

[duluter]

Whoa. Cool idea. I'll give it a whirl and post back.

Thanks,

Duluter

 

[CameronM]

As suggested, you are going to have to create a function that uses a Recordset to loop through the results of the query and joins (concatenates) them together.

For example:

Public Function Concatenate(strQueryName As String, strDelim As String) As String
    Dim rst As DAO.Recordset
    Dim strResults As String
 
    'open a recordset based on the query
    Set rst = CurrentDb.QueryDefs(strQueryName).OpenRecordset
 
    'loop though the query and build the results string
    'on the first time through the loop do not add the delimiter
    While Not rst.EOF
        If Len(strResults) > 0 Then
            strResults = strResults & strDelim & rst("Field1")
        Else
            strResults = rst("Field1")
        End If
        rst.MoveNext
    Wend
 
    'return the results string
    Concatenate = strResults
End Function

This could be called from within you codes as follows:
...Concatenate("qryConcatenate", ",")

 

[MatMac]

Cameron

Thanks for your reply to this thread. I am trying to do the same thing, and am trying use the function you suggest.

Problem is, when the function is called I receive an error message saying "too few parameters 1 parameter is expected". The debugger highlights this line...

Set rst = CurrentDb.QueryDefs(strQueryName).OpenRecordset

My query is called PAAF_CoAp2 and I am calling the function using...

Me![Test] = Concatenate("PAAF_CoAp2", ",")

I.e. trying to set the value of Test to the concatenated string.

Appologies - my VB is lousy!! ;-)

Any ideas?

Many thanks

Mat.

 

[CameronM]

You get this error if the query you are trying to send to the function includes parameters that come from a form, such as WHERE [Field1] = Forms!MyForm.txtField1.

Does your query PAAF_CoAp2 get any values from forms?

 

[MatMac]

Hi Cameron. Thanks for your reply. Since I posted my original thread, I have changed my system somewhat.

What I ultimately need to do is enter the concatenated record fields into a sub-report. I have therefore entered the following as the control source for the subreport field:

=Concatenate("PAAF_CoAp","',")

I have also added the Concatenate function into the subreport VB.

After your reply above, I have entered a parameter into the underlying query:

PARAMETERS [Forms]![project]![projID] Long;
SELECT [appTitle] & " " & [appFirst] & " " & [appSur] AS name, ProjAppList.lead, project.projID, [orgDept] & ", " & [orgName] AS dept
FROM project INNER JOIN (Organisation INNER JOIN (Applicant INNER JOIN ProjAppList ON Applicant.appID = ProjAppList.appID) ON Organisation.orgID = Applicant.orgID) ON project.projID = ProjAppList.projID
WHERE (((ProjAppList.lead)="Co-ap") AND ((project.projID)=[Forms]![project]![projID]));

This stops the system asking for projID.

My report and subreport opens, but I just get the word "#error" in the field where the concatenated records should go.

Can't quite figure out what I am mising.

Many thanks. Mat.

 

[MatMac]

Just tried it the way I did before. Despite the parameter, I still get...

"Too few Parameters, expected 1"

 

[MatMac]

OK - finally managed to solve the problem. Here it is in case anyone else needs to know.

What I want to do is to concatenate the contents of a single field from all rows of a parameter query and insert them into a report field.

Solution (VB is based on Cameron's above):

Report field is set up as an unbound textbox.

The following VB is run on the "OnActivate" report event:

PAAF_CoAp_Name is the name of the query
name is the name of the field I want concatenated
CoApName is the name of the report text box
[Forms]![project]![projID] is the name of the parameter in the PAAF... query

Private Sub Report_Activate()
Dim db As Database
Dim rsQuery As QueryDef
Dim rsQuerySet As Recordset
Set db = CurrentDb()
Dim strResults As String

Set rsQuery = db.QueryDefs![PAAF_CoAp_Name]
rsQuery![Forms!project!projID] = Forms![Project]![projID]
Set rsQuerySet = rsQuery.OpenRecordset()

While Not rsQuerySet.EOF
If Len(strResults) > 0 Then
strResults = strResults & ", " & rsQuerySet("name")
Else
strResults = rsQuerySet("name")
End If
rsQuerySet.MoveNext
Wend
CoApname = strResults
End Sub

 

[CameronM]

Mat,

Thanks for posting the updated code, I had not thought of declaring the values for the parameters with rsQuery![Forms!project!projID] = Forms![Project]![projID]. Man, now I have to go and rewrite all those functions where I created the recordset based on a SQL string with the values from the form

 

[LPurvis]

That particular syntax is perhaps just a little obscuring of the underlying process.
As the parameters collection is the default child collection for a querydef object then the statement
rsQuery![Forms!project!projID] = Forms![Project]![projID]
is analogous to
rsQuery.Parameters("Forms!project!projID") = Forms![Project]![projID]

It's the required evaluation of parameters in a query when executed into a recordset (either DAO or ADO).
It's an old issue - and there are many threads out there dealing with it (it feels like I've been involved in at least a thousand of them lol).
For example here's a thread at AWF which discussed the subject - and where I offer my usual fExecuteQuery function to simplify matters.
http://www.access-programmers.co.uk/forums/showthread.php?t=158354

All that said - this iterative DAO method isn't the only way to concatenate multiple row values. ADO recordset offer the GetString method which does exactly that.
Used something like...

Function fConcatFields(strSQL As String, _
                        Optional strColDelim As String = ",", _
                        Optional strRowDelim As String = ",", _
                        Optional blnNoRecsMessage As Boolean = False) As String
On Error GoTo errHere
    Dim strList As String
    Dim intAns As Integer
    
    strList = CurrentProject.Connection.Execute(strSQL).GetString(2, , strColDelim, strRowDelim)
    fConcatFields = Left(strList, Len(strList) - Len(strRowDelim))
    
    Exit Function
    
errHere:
    If Err = 3021 Then
        'Optional - you might not want this being highlights if it doesn't matter
        If blnNoRecsMessage Then
            MsgBox "Passed SQL did not return any records", vbInformation
        End If
    ElseIf Err = -2147217904 Then
        MsgBox "Passed SQL contained an unrecognized field name", vbInformation
    ElseIf Err = -2147217865 Then
        MsgBox "Passed SQL contained an unrecognized table name", vbInformation
    ElseIf Err = -2147217900 Then
        intAns = MsgBox("Passed SQL contained a syntax error" & vbCrLf & _
                        "Do you want to see the error message?", vbInformation + vbYesNo + vbDefaultButton2)
        If intAns = vbYes Then
            MsgBox Err & ": " & Err.Description, vbInformation
        End If
    Else
        MsgBox Err & ": " & Err.Description, vbExclamation
    End If
End Function

Note that the above function doesn't also allow for parameters in the query.
But, by way of example, you can use much the same method to evaluate such form parameters as in the DAO example earlier...

    Dim cmd As New ADODB.Command
    Dim prm As ADODB.Parameter
    Dim rst As New ADODB.Recordset
    
    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "SELECT * FROM YourQueryName"

    For Each prm In cmd.Parameters
        prm.Value = Eval(prm.Name)
    Next
    
    rst.Open cmd, , adOpenKeyset

And so on.

Cheers.