Object required when using GetList function for concatenation-CodePudding

OBJECTIVE: To concatenate row values

Record in COMP table
    player_id        cc_number
    1                123
    2                152
    1                254
    2                154
    3                256

Result to be
    player_id        cc_number
    1                123, 254
    2                152, 154
    3                256

CODE:

MYSQL = "Select T.player_id, "
MYSQL = MYSQL & GetList("Select cc_number From COMP As T1 Where T1.player_id = " & [T].[player_id], "", ", ") & " AS NewCCNumber"
MYSQL = MYSQL & "From COMP AS T"
MYSQL = MYSQL & "Group By T.player_id"

ERROR: i used GetList function but it gives me error Object Required.

CodePudding user response：

Need space in front of FROM and GROUP or after NewCCNumber and T. Otherwise text strings run together. Also, the GetList() function should probably be embedded in string

MYSQL = "Select T.player_id, "
MYSQL = MYSQL & "GetList('Select cc_number From COMP As T1 Where T1.player_id = " & [T].[player_id] & "', '', ', ') As NewCCNumber "
MYSQL = MYSQL & "From COMP As T "
MYSQL = MYSQL & "Group By T.player_id"