当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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 same 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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: