Combine multiple rows into single output

,

We can use this function to combine multiple rows into single one. Generally to create comma separated list for each unique item. So this can create comma separated product list for any given category.

The same can be used to make a list of items in a given order.


Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/

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

SELECT	DISTINCT CategoryID, dbo.ProductList (CategoryID) AS ProductList
FROM Products
GO

Rate

5 (3)

Share

Share

Rate

5 (3)