I'm a Junior DBA (with no mentor) struggling with a development issue. We store products in our db. Our products are stored in a produts table that includes prod_id, attr_cd and attr_val. Any given product could have multiple rows in that table meaning any given product could have many attributes. Some of the attributes are searchable. We maintain a text search engine with interMedia. I don't want to have manintain an interMedia index on attr_val because not all of the values are searchable and I'd like to have it one row per prod_id. There is another table which stores whether or not an attribute is searchable. I've designed a search table and have created trigger which puts an insert into search based on an insert of searchable attributes on our products table. What I'm struggling with is how to concatenate the values into one row per distinct prod_id (without doing a multiple self-join). Redesign of the products table is not an option. I'm started to look at collections and records but I'm not even sure I'm on the right track. Any suggestions would be most appreciated. Thx.
