Re: Tricky Sql Question

From: <holderm_at_mailexcite.com>
Date: Thu, 30 Apr 1998 17:26:50 -0600
Message-ID: <6iatr9$t09$1@nnrp1.dejanews.com>

In article <6ia5qh$323_at_explorer.csc.com>#1/1,   rkolluru@.csc.com 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
>

You could do this with SQL by the following:

SELECT MAX(DECODE(num, 1, text, NULL)) ||
       MAX(DECODE(num, 2, text, NULL)) ||
       MAX(DECODE(num, 3, text, NULL))    "Nice Column Alias"
     , qty          "Quantity"

FROM junk
GROUP BY qty

The problem is that this only works if the maximum value for num is three.

If it's open ended you would have to use PL/SQL and two cursors... One selecting the distinct values for qty, the next getting the text for that qty ORDERed BY num, then append text to a character variable.

• Mike

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 30 1998 - 18:26:50 CDT