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):

locations
id
name
address

agents
id
name
phone

location_agent_assoc
location_id
agent_id

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 TorontoAsked:
Who is Participating?
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 = loc.id
                   FOR XML PATH('')), 2, 99999) AS agent_ids
FROM    locations loc

Open in new window

0
APD TorontoAuthor Commented:
What is that?
0
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

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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 = loc.id
                   FOR XML PATH('')), 2, 99999) AS agent_ids
FROM    locations loc

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
dsacker,

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  l.id, 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.

Thanks,

Mike
0
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? :)
0
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 = loc.id

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
You have a partner in this. Me.
0
APD TorontoAuthor 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.
0
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:

SELECT  loc.id,
        loc.name,
        loc.address,
        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 = loc.id
GROUP BY loc.id,
        loc.name,
        loc.address

Open in new window

0

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 TorontoAuthor Commented:
Thank you!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.