How to aggregate data into view with the same ID

I have an existing view that returns data in the following format based on aggregating option names that have the sam开发者_StackOverflow社区e product_id. The view name is "vProdOptions"

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 an aggregated view formatted like this?

  OPTIONS_AVAIL                                           Product_ID

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

  SMALL (6-10), MEDIUM (10-14)                            166382

I am using MS SQL 2k5.

I am creating a new question here based on comments from these two questions. I realized that I needed a view instead.

Aggregate data from view as UDF to use in select statement

error with sql function creation

---

You could select the distinct products, and cross apply them on a function that computes a string of options:

select *
from (select distinct product_id from @t) a
cross apply (
    select option_name + ', ' as [text()]
    from @t b
    where a.product_id = b.product_id
    for xml path('')
) c ( Options )

-->

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

Code to create example:

declare @t table (option_name varchar(30), product_id int)

insert @t select 'XSMALL (2-6)', 17854
union all select 'SMALL (6-10)', 17854
union all select 'MEDIUM (10-14)', 17854
union all select 'LARGE', 18232

---

Pivoting is not a terribly efficient way to go in SQL. Personally, I'd leave your view alone and pivot the data through the application.