Aggregate data from view as UDF to use in select statement

Question

I have an existing view that returns data in the following format.

option_name           product_id

XSMALL (2-6)          17854
SMALL (6-10)          17854
MEDIUM (10-14)      17854
LARGE                 18232

How do I return this data in a comma separated field formatted like this, based on sending a product_id to the function?

XSMALL (2-6), SMALL (6-10), MEDIUM (10-14)

I am using MS SQL 2k5.

Answer 1

You can use FOR XML PATH in SQL Server 2005 and up:

CREATE FUNCTION dbo.GetProductNames(@ProductID int)
RETURNS TABLE
AS
SELECT
    Options = SUBSTRING((
        SELECT ', ' + o2.option_name
        FROM options AS o2
        WHERE o2.product_id = o1.product_id
        FOR XML PATH('')), 3, 1000)
FROM options AS o1
WHERE o1.product_id = @ProductID

Answer 2

**

declare @option_name varchar(1000) select @option_name = isnull(@option_name+',','') + option_name from tablename select @option_name

**