Combining column values across rows
Maky
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
Niall Litchfield
short while a\go on this group it should be findable thru google.
2. No views take up no space in the database worth caring about.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Legal disclaimer required by my employer
****************************************************************************
**
This email contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or professional
privilege.
Any dissemination, distribution, copyright or use of this communication
without prior permission of the addressee is strictly prohibited.
****************************************************************************
**
"Maky" <ma...@lemur.org.uk> wrote in message
news:tn55ltb...@xo.supernews.co.uk...
Thomas Kyte
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$tk...@ORA817.US.ORACLE.COM> create table product
2 ( pname varchar2(10),
3 product_id int primary key
4 );
Table created.
ops$tk...@ORA817.US.ORACLE.COM> create table category
2 ( cname varchar2(10),
3 category_id int primary key
4 );
Table created.
ops$tk...@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$tk...@ORA817.US.ORACLE.COM>
ops$tk...@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$tk...@ORA817.US.ORACLE.COM>
ops$tk...@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$tk...@ORA817.US.ORACLE.COM>
ops$tk...@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:
tk...@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.
tk...@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.
tk...@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.
tk...@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 (tk...@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