Return related values as array

Silly it may be but sometimes de-normalized relationships make more sense to the end-user. The following function presents a way to list related records (in the Northwind database) in a flat table, where in this case the names of products in an order can be listed in a single column as comma-delimited values. Also known as: aggregate concatenation.

create function dbo.fnGet_ProductName_asArray
 (
 @OrderID int
 ,@Separator nvarchar(16) = null
 )
returns nvarchar(4000)
as
begin
 declare @productNameArray nvarchar(4000)

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

 set @productNameArray = N''

 select @productNameArray
   = @productNameArray
   + @Separator
   + convert(nvarchar(4000), isnull(dbo.Products.ProductName, N''))
  from dbo.[Order Details]
   inner join dbo.Products
     on dbo.Products.ProductID = dbo.[Order Details].ProductID
  where (dbo.[Order Details].OrderID = @OrderID)
  order by dbo.Products.ProductName

 set @productNameArray = ltrim(rtrim(substring(@productNameArray,
                       len(@Separator) + 1, len(@productNameArray))))

 return @productNameArray
end
go

Please, use this function for presentation purposes only. Better still - use appropriate presentation components to do this on the client-side.

WARNING!
There are known issues with aggregate concatenation in T-SQL:

• PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location;
• Bug Details: Expression in ORDER BY clause causes self-referencing variables in the SELECT clause to evaluate only once rather than once per row.

Thanks to Erland Sommarskog for pointing them out to me.

Still, here's an example of use:

select dbo.[Order Details].OrderID
 ,dbo.fnGet_ProductName_asArray
  (dbo.[Order Details].OrderID, N' | ') as ProductNames
 from dbo.[Order Details]
  inner join dbo.Products
    on dbo.Products.ProductID = dbo.[Order Details].ProductID
 group by dbo.[Order Details].OrderID

ML

p.s. In SQL Server 2005 there are alternatives...