Combining column values across rows

From: Maky <maky_at_lemur.org.uk>
Date: Thu, 9 Aug 2001 14:58:14 +0100
Message-ID: <tn55ltbl00t8c2@xo.supernews.co.uk>

Hi,

I hope someone here can help me - I know very little about Oracle or SQL, but have found myself involved in a project where no one else knows anything about it either. =/

The problem is that we have a product table which has a many-to-many relation to a category table (joined through a third "parent" table):

    [product] ---< [parent] >--- [category]

We want to get a view where each product occupies just one row, and any multiple category values are combined into a single value, eg (concatenating with commas):

   Product Category

---

   cheese dairy
   cheese solid

   milk        dairy
   milk        liquid
   beer       liquid

will become

   Product Category

---

   cheese dairy, solid

   milk        dairy, liquid
   beer        liquid

(This is to make it easier to feed to an Inktomi full-text indexer.)

Is there any way to do this, preferably in SQL?

Also, am I right in thinking that creating a view does not take up any significant storage space in the database?

Thanks for any help,
Maky Received on Thu Aug 09 2001 - 08:58:14 CDT