Posted by M@ on 06/26/07 21:54

found a really good alternative on sqlteam.com

USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20),
P.ProductName)
FROM dbo.[Order Details] OD
JOIN dbo.Products P
ON P.ProductID = OD.ProductID
WHERE OD.OrderID = @OrderID
ORDER BY P.ProductName

RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
GO

[Back to original message]