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.
精彩评论