Aggregate concatenation in SQL 2005

In a previous post I've discussed aggregate concatenation and the issues related to that particular solution.

SQL Server 2005 provides new possibilities of implementing safer and more efficient methods. The most obvious one would seem to be in designing a user-defined CLR function, since .Net 2.0 seems like a pretty efficient place to manipulate strings (in respect to T-SQL). There is, however, another way.

As Tony Rogerson writes in his post (and in a few posts to the newsgroups) the FOR XML clause and the resulting XML can be used in SQL Server 2005 to provide a safer T-SQL alternative to the function I've posted some time ago.

Here is an example based on the AdventureWorks database:

create function dbo.fnOrderDetail_Products_asArray
 (
 @PurchaseOrderID int
 ,@separator  varchar(8) = null
 )
returns varchar(max)
as
begin
 declare @resultString varchar(max)

 if (@separator is null)
  begin
   set @separator = ', '
  end

 select @resultString
   = (
   select [data()]
     = '<>'
     + Production.Product.[Name]
    from Purchasing.PurchaseOrderDetail
     inner join Production.Product
       on Production.Product.ProductID
         = Purchasing.PurchaseOrderDetail.ProductID
    where (Purchasing.PurchaseOrderDetail.PurchaseOrderID
       = @PurchaseOrderID)
    order by Production.Product.[Name]
    for xml path(''), type
   ).value('.', 'varchar(max)')

 set @resultString = replace(@resultString, ' <>', @separator)
 set @resultString = replace(@resultString, '<>', @separator)
 set @resultString
   = substring(@resultString, charindex(@separator,
   @resultString) + len(@separator), len(@resultString))
 set @resultString = ltrim(rtrim(@resultString))

 return @resultString
end
go

The function returns the names of the product(s) in a given Purchase Order as a delimited string.

An example of use:

select Purchasing.PurchaseOrderDetail.PurchaseOrderId as PurchaseOrderId
 ,Purchasing.PurchaseOrderDetail.DueDate as DueDate
 ,dbo.fnOrderDetail_Products_asArray
  (Purchasing.PurchaseOrderDetail.PurchaseOrderId, ' - ') as Products
 from Purchasing.PurchaseOrderDetail

ML