April 4, 2012 at 3:24 pm
Hello all
I have agents in agent table
and also cities they can sell for
i have a cross ref table with cityid and agentid in Agentcity Xref table.
For example
AGENT TABLE
AGENTID Agentname Dob Gender
1 x 1/1/1900 M
2 y 2/2/1900 F
City table
Cityid cityname State
1 Chicago IL
2 bloomington IL
3 St louis MO
AgentcityXref table
Agentid cityid
1 1
1 2
1 3
2 1
2 3
So i want the result set as from these tables as
agentid, agentname, concatenatedcities
1 x bloomington,chicago,stlouis
2 y chicago,st.louis
I want the concatenatedcities Column in the alphabetical order too..
Can you help me on this????
April 4, 2012 at 4:30 pm
This should help.
declare @a table (agentid int, name varchar(10))
declare @C table (cityid int, name varchar(15))
declare @x table (agentid int, cityid int)
insert into @a
select 1, 'x'
union all select 2, 'y'
insert into @C
select 1, 'chicago'
union all select 2, 'bloomington'
union all select 3, 'st louis'
insert into @x
select 1,1
union all select 1,2
union all select 1,3
union all select 2,1
union all select 2,3
;with combined as
(
select a.agentid, a.name agentname, c.name cityname
from @a a
join @x x on x.agentid = a.agentid
join @C c on c.cityid = x.cityid
)
select o.agentid, o.agentname,
STUFF((SELECT ','+ i.cityname
FROM combined i
WHERE i.agentid = o.agentid
ORDER BY i.cityname
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
, 1,1,'') cities
from combined o
group by o.agentid, o.agentname
April 4, 2012 at 8:19 pm
This one works perfect but can you tell me how for XML works??
April 5, 2012 at 8:03 am
FOR XML is used to return query results in XML format. You can read more about it in the documentation. I don't know the in's and out's of it as I'm sure many other people do since I don't deal with XML much and have only used the FOR XML PATH function the same way you have, to return the data in a delimited format and convert it to character data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply