Re: Tricky Sql Question

Hi.

I'd use PL/SQL - something along the lines of:

declare
  quant integer;
  cursor c1 is select distinct qty from junk_tab order by qty;   cursor c2 is select text from junk_tab where qty = :quant order by text;
  concat varchar2(32); -- Change this to > max. values begin
  dbms_output.enable;
  for qt in c1 loop
    quant := qt.qty;
    concat := '';
    for tx in c2 loop
      concat := concat || tx.text;
    end loop;
    dbms_output.put_line(rpad(concat,32)||lpad(quant,5));   end loop;
  dbms_output.disable();
end;

(Pseudocode - not tested!) This could easily be made into a stored procedure, returning a varchar2 type for example.

HTH Jamie Keir

dmarsha3 wrote:
>
> Group :
>
> I have a table junk_tab(text,num,qty)
>
> Text num Qty
> ----------------
> A 1 5
> B 2 5
> C 3 5
> A 1 4
> B 2 4
> E 1 6
> F 2 6
>
> and I would like to have a result like
>
> ABC 5
> AB 4
> EF 6
>
> Could somebody give a tip,direction or solution for this problem.
>
> Thanks in advance
>
> rama
> rkolluru_at_csc.com

-- 
  _______________________________________________________________
 /###############################################################\
+--------------------+---------------+----------------------------+
| Oracle 7     DBA   | Digital Unix  | Jamie Keir                 |
| Developer*2000     | Windows NT 4  | jkerr_at_jameswatt.ac.uk      |
| PL*SQL             | AmigaOS 3     |                            |
| Unix Admin         | Linux         |                            |
| Network Support    | ADA           | ...now if I could just get |
|                    | C / C++       | them to *pay* me for it... |
| Alpha AXP          | M68040 Asm    |                            |
+--------------------+---------------+----------------------------+