Re: Tricky Sql Question

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Thu, 30 Apr 1998 21:08:12 GMT
Message-ID: <3548e832.6174489@www.sigov.si>

On 30 Apr 1998 15:36:49 GMT, dmarsha3_at_csc.com (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.

1. General sollution: use PL/SQL
2. Somewhat clumsy, but "pure" SQL sollution:

SQL> SELECT DISTINCT x1.text||x2.text||x3.text||x4.text||x5.text,   2 junk_tab.qty
  3 FROM junk_tab,

  4       (SELECT text, qty FROM junk_tab WHERE num = 1) x1,
  5       (SELECT text, qty FROM junk_tab WHERE num = 2) x2,
  6       (SELECT text, qty FROM junk_tab WHERE num = 3) x3,
  7       (SELECT text, qty FROM junk_tab WHERE num = 4) x4,
  8       (SELECT text, qty FROM junk_tab WHERE num = 5) x5
  9  WHERE junk_tab.qty = x1.qty(+)
 10    AND junk_tab.qty = x2.qty(+)
 11    AND junk_tab.qty = x3.qty(+)

 12 AND junk_tab.qty = x4.qty(+)
 13 AND junk_tab.qty = x5.qty(+)
 14 /

TEXT QTY
----- ---------

AB            4
ABC           5
EF            6

SQL> This example presumes that there could not be more than 5 members in each Qty group. Works with Oracle 7.2 and higher.

>Thanks in advance
>
>rama
>rkolluru_at_csc.com

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Apr 30 1998 - 16:08:12 CDT