How to Combine Multiple Rows Data into single Record or String based on a common field.

Hellow Folks.

Here is the Original Data in my single SQL 2005 Table:

Department: Sells:

1 Meat

1 Rice

1 Orange

2 Orange

2 Apple

3 Pears

The Data I would like read separated by Semi-colon:

Department: Sells:

1 Meat;Rice;Orange

2 Orange;Apple

3 Pears

I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..

Hi,

you can use the following Function in SQL server:

USE NORTHWIND
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + ProductName
FROM Products
WHERE CategoryID = @.CategoryID
ORDER BY ProductNameASC
RETURN @.Products
END
GO
SELECTDISTINCT CategoryID, dbo.ProductList (CategoryID)AS ProductList
FROM Products
GO

and this is based on your table: 

USE NORTHWINDCS
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + sells
FROM table1
WHERE Department = @.CategoryID
ORDER BY sellsASC
RETURN @.Products
END
GO
SELECTDISTINCT Department, dbo.ProductList (department)AS ProductList
FROM table1
GO
 

thanks

|||

SharpGuy, your solution works. Thanks and have a great Thanksgiving...