Combine multiple rows into single output

USE NORTHWIND
GO

CREATE FUNCTION ProductList (@CategoryID INT)
RETURNS VARCHAR(1000)
AS 
BEGIN
DECLARE @Products VARCHAR(1000)

SELECT@Products = COALESCE(@Products + ', ', '') + ProductName
FROM Products
WHERE CategoryID = @CategoryID
ORDER BY ProductName ASC

RETURN @Products 
END
GO

SELECTDISTINCT CategoryID, dbo.ProductList (CategoryID) AS ProductList
FROM Products
GO