asked on

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

dsacker

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

APD Toronto

ASKER

What is that?

Mike Eghtebas

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

dsacker

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

Mike Eghtebas

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

dsacker

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

dsacker

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

Mike Eghtebas

You have a partner in this. Me.

APD Toronto

ASKER

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.

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

APD Toronto

ASKER

Thank you!