Getting Records as comma-seperated list

Hi Experts,

In my applcation, I have agents that are associated with multiple of loations, so my database is as follows (in bold are my table names, followed by field names):




so my last table is a linked table.

My question is, how can I SELECT * FROM locations, and all the agent_id(s) from the location_agent_assoc table as a comma-separated list?

I am hoping to get a row source similar like

id   name           address      agent_ids
1    the mall       123abc       1,7,8,25
2   the musium  456no st    32,2,78

thank you
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
You'll like this. It uses the FOR XML PATH to do the magic. :)

SELECT  loc.*,
        SUBSTRING((SELECT ',' + CONVERT(varchar(8), id)
                   FROM   location_agent_assoc
                   WHERE  location_id =
                   FOR XML PATH('')), 2, 99999) AS agent_ids
FROM    locations loc

Open in new window

APD TorontoSoftware DeveloperAuthor Commented:
What is that?
Mike EghtebasDatabase and Application DeveloperCommented:
Hi dsacker,

I tested your solution, it is producing:

1	the mall	123abc	1,1,1,1
2	the musium	456no st	2,2,2

Open in new window

Here is tsql for temp tables for your own testing.
create table #locations(id int, name varchar(20), [address] varchar(20));
create table #agents(id int, name varchar(20), phone varchar(20));
create table #location_agent_assoc(location_id int, agent_id int);

insert #locations(id, name, [address]) values
(1, 'the mall', '123abc')
, (2, 'the musium', '456no st');

--insert #agents(id, name, phone) values

insert #location_agent_assoc(location_id, agent_id) values
(1, 1)
, (1, 7)
, (1, 8)
, (1, 25)
, (2,32)
, (2,2)
, (2,78)

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dsackerContract ERP Admin/ConsultantCommented:
Good catch eghtebas, it probably would help if I selected the agent_id :)

SELECT  loc.*,
        SUBSTRING((SELECT ',' + CONVERT(varchar(8), laa.agent_id)
                   FROM   location_agent_assoc laa
                   WHERE  laa.location_id =
                   FOR XML PATH('')), 2, 99999) AS agent_ids
FROM    locations loc

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:

I am new to xpath. could you please take a look to see if the following solution could be made to work also?

now producing:

1      the mall      123abc      1, 7, 8, 25, 32, 2, 78,
2      the musium      456no st      1, 7, 8, 25, 32, 2, 78,

SELECT, name, l.[address]
     , (
        SELECT cast(agent_id as varchar(4)) + ', '
             FROM   #location_agent_assoc
             FOR XML PATH('')
	    ) AS agent_ids
FROM    #locations l

Open in new window

It seems it is missing WHERE clause of some sort.


dsackerContract ERP Admin/ConsultantCommented:
Compare yours with mine above yours. You'll need to (1) change agent_id + ' '  to ' ' + agent_id, and (2) wrap it with a SUBSTRING, like I do, in order to not have a space prefixing or suffixing. Of course, when using spaces, you can also forego the SUBSTRING for a RTRIM(LTRIM()) it.

You may need to CONVERT/CASE the agent_id. I wouldn't be surprised but that it is numeric.

After all that, you have accomplished what I've given, except you're using spaces instead of commas between the list of values.

But the FOR XML PATH is totally cool, eh? :)
dsackerContract ERP Admin/ConsultantCommented:
Ah, you know what! I just noticed the author tagged his question as MySQL (lol). MySQL provides a GROUP_CONCAT function that does the equivalent of the little SQL Server XML trick.

That actually may be easier:

SELECT  loc.*,
        GROUP_CONCAT(laa.agent_id) AS agent_ids
FROM    locations loc
INNER JOIN location_agent_assoc laa ON laa.location_id =

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
You have a partner in this. Me.
APD TorontoSoftware DeveloperAuthor Commented:
From the last code this is my result

2	Hyatt	HOK	8	370 King St. W.		9:15:00	0	1	0		3,4,13,1,1,20,20,20,20,20,20,20,20,20,20,20,21,21,21,21,21,21,21,21,21,21,21,21,22,22,22,22,22,22,22,22,22,22,22,22,22,21,1,20,4,13,9,21,23,21,1,1,21,31,33,1,36,21,1,1,22,1,30,59,61,65,22,76,106,121,153,22,217,217,233,240,243,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,254,269,270,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,274,27

Open in new window

First in my association table I do not have any duplicate agent ids for a single location
Secondly, I only get 1 row (1 location), but I have over 300.
dsackerContract ERP Admin/ConsultantCommented:
Looks like it wants a GROUP BY as well as an inside DISTINCT and ORDER BY. It's probably better to explicitly name your fields:

        GROUP_CONCAT(DISTINCT laa.agent_id ORDER BY laa.agent_id) AS agent_ids
FROM    locations loc
INNER JOIN location_agent_assoc laa ON laa.location_id =

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.