I have an existing view that returns data in the following format based on aggregating option names that have the same product_id. The view name is "vProdOptions"
XSMALL (2-6) 17854
SMALL (6-10) 17854
MEDIUM (10-14) 17854
How do I return this data in an aggregated view formatted like this?
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.