Re: Combining column values across rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 9 Aug 2001 10:15:54 -0700
Message-ID: <9kugga018ue@drn.newsguy.com>

In article <tn55ltbl00t8c2_at_xo.supernews.co.uk>, "Maky" says...
>
>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
>
>
>

sigh, never a version in sight

see

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:229614022562

it shows two ways to do this (using a tiny bit of PLSQL that'll be called from SQL). In Oracle8i release 2 (816 and up), if you know the MAXIMUM number of categories you ever expect, you can use sql as well. For example given the following example where I know I have at most 10 categories per product, I can:

ops$tkyte_at_ORA817.US.ORACLE.COM> create table product   2 ( pname varchar2(10),
  3 product_id int primary key
  4 );

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM> create table category   2 ( cname varchar2(10),
  3 category_id int primary key
  4 );

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM> create table parent   2 ( product_id references product,
  3 category_id references category,
  4 constraint parent_pk primary key(product_id,category_id)   5 )
  6 organization index;

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> begin

  2      for i in 1 .. 10 loop
  3          insert into product values ( 'Product-'||i, i );
  4          insert into category values ( 'Cat-'||i, i );
  5      end loop;
  6  
  7      for i in 1 .. 100 loop
  8          begin
  9              insert into parent values
 10              ( dbms_random.value( 1, 10 ), dbms_random.value(1,10) );
 11          exception
 12              when dup_val_on_index then
 13                  null;
 14          end;
 15      end loop;

 16 end;
 17 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> select pname, cname   2 from product, parent, category
  3 where product.product_id = parent.product_id

  4     and parent.category_id = category.category_id
  5     and pname in (  'Product-1', 'Product-2' );

PNAME      CNAME
---------- ----------
Product-1  Cat-1
Product-1  Cat-5
Product-1  Cat-6
Product-1  Cat-7
Product-1  Cat-8
Product-2  Cat-3
Product-2  Cat-4
Product-2  Cat-5
Product-2  Cat-6

Product-2 Cat-8
Product-2 Cat-9

11 rows selected.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> select pname,

  2    rtrim(max( decode(r,     1, cname || ',', null ) ) ||
  3          max( decode(r,  2, cname || ',', null ) ) ||
  4          max( decode(r,  3, cname || ',', null ) ) ||
  5          max( decode(r,  4, cname || ',', null ) ) ||
  6          max( decode(r,  5, cname || ',', null ) ) ||
  7          max( decode(r,  6, cname || ',', null ) ) ||
  8          max( decode(r,  7, cname || ',', null ) ) ||
  9          max( decode(r,  8, cname || ',', null ) ) ||
 10          max( decode(r,  9, cname || ',', null ) ) ||
 11          max( decode(r, 10, cname || ',', null ) ) , ',' ) all_of_them
 12  from ( select pname, cname,
 13                row_number() over ( partition by pname
 14                                        order by cname ) r
 15           from product, parent, category
 16          where product.product_id = parent.product_id
 17            and parent.category_id = category.category_id
 18            and pname in (  'Product-1', 'Product-2' )
 19       )

 20 group by pname;

PNAME ALL_OF_THEM

---------- ------------------------------------------

Product-1 Cat-1,Cat-5,Cat-6,Cat-7,Cat-8 Product-2 Cat-3,Cat-4,Cat-5,Cat-6,Cat-8,Cat-9

Just to show off a new 9i feature (you need Oracle9i and up to do this one), that removes the need to know how many categories you are going to have and gives you a generic "aggregate string" routine that can be used on any table/column -- we can write a user defined aggregate:

tkyte_at_TKYTE9I.US.ORACLE.COM> create or replace type agg_string_type as object   2 (
  3 string varchar2(4000),
  4

  5     static function
  6          ODCIAggregateInitialize(sctx IN OUT agg_string_type )
  7          return number,
  8
  9     member function
 10          ODCIAggregateIterate(self IN OUT agg_string_type,
 11                               value IN varchar2 )
 12          return number,
 13
 14     member function
 15          ODCIAggregateTerminate(self IN agg_string_type,
 16                                 returnValue OUT varchar2,
 17                                 flags IN number)
 18          return number,
 19
 20     member function
 21      ODCIAggregateMerge(self IN OUT agg_string_type,
 22                         ctx2 IN agg_string_type)
 23      return number

 24
 25 );
 26 /

Type created.

tkyte_at_TKYTE9I.US.ORACLE.COM> create or replace type body agg_string_type   2 is
  3
  4 static function ODCIAggregateInitialize(sctx IN OUT agg_string_type )   5 return number
  6 is
  7 begin

  8      sctx := agg_string_type( NULL );
  9      return ODCIConst.Success;

 10 end;
 11
 12 member function ODCIAggregateIterate(self IN OUT agg_string_type,

 13                                       value IN varchar2 )

 14 return number
 15 is
 16 begin

 17      self.string := self.string || ',' || value;
 18      return ODCIConst.Success;

 19 end;
 20
 21 member function ODCIAggregateTerminate(self IN agg_string_type,

 22                                         returnValue OUT varchar2,
 23                                         flags IN number)

 24 return number
 25 is
 26 begin

 27      returnValue := ltrim(self.string,',');
 28      return ODCIConst.Success;

 29 end;
 30
 31
 32 member function ODCIAggregateMerge(self IN OUT agg_string_type,

 33                                     ctx2 IN agg_string_type)

 34 return number
 35 is
 36 begin

 37      self.string := self.string || ',' + ltrim(ctx2.string,',');
 38      return ODCIConst.Success;

 39 end;
 40
 41 end;
 42 /

Type body created.

tkyte_at_TKYTE9I.US.ORACLE.COM> CREATE or replace   2 FUNCTION agg_string(input varchar2 )   3 RETURN varchar2
  4 AGGREGATE USING agg_string_type;
  5 /

Function created.

tkyte_at_TKYTE9I.US.ORACLE.COM> select pname, agg_string( cname ) all_of_them   2 from product, parent, category
  3 where product.product_id = parent.product_id

  4     and parent.category_id = category.category_id
  5     and pname in (  'Product-1', 'Product-2' )

  6 group by pname
  7 /

PNAME ALL_OF_THEM

---------- ------------------------------------------

Product-1 Cat-1,Cat-5,Cat-6,Cat-9,Cat-7 Product-2 Cat-2,Cat-8,Cat-10,Cat-9,Cat-7,Cat-3,Cat-4

           ,Cat-6,Cat-5

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Aug 09 2001 - 12:15:54 CDT