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:

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...