Re: Combine records

"NoName" <nobody_at_nowhere.com> wrote in message news:<br9i74$lm6$1_at_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

Thanks a lot for your help. It works well.

Nick Received on Thu Dec 11 2003 - 15:32:18 CST