Re: Combine records

From: NoName <nobody_at_nowhere.com>
Date: Thu, 11 Dec 2003 12:07:58 +0100
Message-ID: <br9i74$lm6$1@grillo.cs.interbusiness.it>

Well, with the following, you can extract only the records with the same value in columns c_id/product/country:

select t1.c_id, t1.product, t1.country, (t1.region || ' - ' || t2.region) region
from mytable t1, mytable t2
where t1.c_id=t2.c_id

   and t1.product=t2.product
   and t1.country=t2.country
   and t1.region<t2.region

or, another solution, is to use the number of a row, and to sort with that number:

select a1.c_id, a1.product, a1.country,
  concat(a1.num1,a2.num2) g, (a1.region || ' - ' || a2.region) region from (select mytable.*, rownum as num1 from mytable) a1,

     (select mytable.*, rownum as num2 from mytable ) a2 where a1.num1<>a2.num2

   and a1.c_id=a2.c_id
   and a1.product=a2.product
   and a1.num1<a2.num2

order by g

Side effect: you must union this select with another one that selects only those "single-records", that is records with different values in either c_id, product or country fields.

The best solution is to use PL/SQL cursor, loops and string manipulation...

Regards Received on Thu Dec 11 2003 - 05:07:58 CST