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