Posted by Erland Sommarskog on 10/01/99 11:19

pb648174 (google@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, esquel@sommarskog.se

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

[Back to original message]