Problem converting rows to a string

[dnsstaiger]

I try to accomplish the following:

I have two tables which are connected via a third table (N:N
relationship):

Table 1 "Locations"
LocationID (Primary Key)

Table 2 "Specialists"
SpecialistID (Primary Key)
Name (varchar)

Table 3 "SpecialistLocations"
SpecialistID (Foreign Key)
LocationID (Foreign Key)
(both together are the primary key for this table)

Issuing the following command

SELECT
L.LocationID , S.[Name]
FROM
Locations AS L
LEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationID
LEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistID

results in the following table:

LocationID | Name
1 Specialist 1
1 Specialist 2
2 Specialist 3
2 Specialist 4
3 Specialist 1
4 Specialist 4

Now my problem: I would like to have the following output:

LocationID | Names
1 Specialist 1, Specialist 2
2 Specialist 3, Specialist 4
3 Specialist 1
4 Specialist 4

....which is grouping by LocationID and concatenating the specialist
names.
Any idea on how to do this?

Thank you very much,
Dennis

[Erland Sommarskog]

(dn********@gmx.net) writes:

Now my problem: I would like to have the following output:

LocationID | Names
1 Specialist 1, Specialist 2
2 Specialist 3, Specialist 4
3 Specialist 1
4 Specialist 4

...which is grouping by LocationID and concatenating the specialist
names.
Any idea on how to do this?

This is one of the rare cases where you need to set up a cursor and
iterate. In SQL 2000 there is no defined way to do this. (There is
a shortcut, but it relies on undefined behaviour, so I don't recommend it.)

In SQL 2005, currently in beta, the story is different. There you
actually have a way to this in a set-based statement, although the
syntax is somewhat bewildering. (It's actually a by-product, of all
the XML stuff they thrown in.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[pb648174]

I typically do things like this in my application code, i.e. while the
locationid is the same, keep tacking values onto the other column's
display in a comma delim format.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

I typically do things like this in my application code, i.e. while the
locationid is the same, keep tacking values onto the other column's
display in a comma delim format.

Yes, that is also a very common advice. But people insists on asking
about how doing this in SQL, that I've given up telling them to use
application code. (And sometimes the application is not any more
sophisticated than Query Analyzer.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[pb648174]

Can you post the SQL 2005 code that will do this? I've been hoping SQL
2005 would have an aggregate function for strings that would turn it
into a delimited string.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

Can you post the SQL 2005 code that will do this? I've been hoping SQL
2005 would have an aggregate function for strings that would turn it
into a delimited string.

Sure, here it is:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

This gives you an output like:

ALFKI 10643,10692,10702,10835,10952,11011
ANATR 10308,10625,10759,10926
ANTON 10365,10507,10535,10573,10677,10682,10856

Now, I did definitely come with this on my own, but I got it from one
of the SQL Server developers.

The part that produces the comma separated list, is the text() function,
which is activated by the XML PATH('') at the bottom. The real point
of text() is probably not to produce a comma separated list, but it's
possible to do it.

Then then comma-separated list is combined with Customers through
CROSS APPLY. APPLY is another operator I have not fully digested
yet, but you use it when you want to call a table-valued functions
with parameters from other columns in the query; something you can't
do in SQL 2000.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp