Posted by Erland Sommarskog on 10/01/47 11:32

laurenq uantrell (laurenquantrell@hotmail.com) writes:
> Currently I'm using a UDF and a physical temp table to accomplish this
> and I want to know if there's any way of doing it in a query or
> subquery instead...

On SQL 2000, not really. (Unless you want to rely on undefined behaviour.)

On SQL 2005, there is actually away. Here I include only a canned example
that I got from one of the SQL Server devs:

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

It's quite appalling, and I still have not learn enough XQuery to
understand how this works - only that it works.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]