Array of Row Values

[TTach]

I have created the following SQL statement to find the Lowest Published price from several price books for a group of products.

select distinct
prc.prod_id,
prc.prod_prce
from
table1 prc,
table2 prod
where
(prc.prod_id=prod.prod_id)
and prod.div='X'
and prc.prod_price_eff_date >= {d '2002-02-01'}
and prc.prod_prce <> 0
group by 1
qualify rank(prc.prod_prce asc) <=1

Now I want to add a third column to the query to create a list(array) of the price books which contain a price for each product. Each product can have 1 or more rows. My Result set should look like:

Prod_ID LowPrice Books
123 .39 book1, book2, book3
456 1.30 book2, book3, book5

Any suggestions on how I can accomplish this? Please keep in mind I do not have CREATE privileges, I need to be able to do this with a SELECT statement.

 

[tdatgod]

Hi,
According to the ANSI SQL forum there is no way to do with SQL without building an all encompassing case statement.

thread220-291724

Maybe with a stored procedure or post processing via AWK.

----