Re: How do I return a column into a row ?

From: MarkyG <markg_at_mymail.tm>
Date: 1 Feb 2002 01:15:17 -0800
Message-ID: <ab87195e.0202010115.5e1da6fe@posting.google.com>

Try this for size.
I'm a bit wary of using NULL in the decode, after all 1 + NULL is null! It works in your case though, try using the sql statement with NULL then with 0 and confirm your results are the same.

create table tmp
(id number, name varchar2(1), qty number);

insert into tmp values(1,'A',4);
insert into tmp values(1,'B',10);
insert into tmp values(1,'C',2);
insert into tmp values(2,'B',5);
insert into tmp values(2,'D',1);
insert into tmp values(3,'A',6);
insert into tmp values(3,'C',2);
insert into tmp values(3,'D',7);

COMMIT; SELECT

     ID, 
     SUM(DECODE(name, 'A', qty, NULL)) a,
     SUM(DECODE(name, 'B', qty, NULL)) b,
     SUM(DECODE(name, 'C', qty, NULL)) c,
     SUM(DECODE(name, 'D', qty, NULL)) d
FROM
     tmp

GROUP BY id;

Result

        ID A B C D ---------- ---------- ---------- ---------- ----------

         1          4         10          2          
         2                     5                     1
         3          6                     2          7

HTH, Mark

C Chang <cschang_at_maxinter.net> wrote in message news:<3C5A121F.370F_at_maxinter.net>...
> I have record set like following:
> ID name Qty
> 1 A 4
> 1 B 10
> 1 C 2
> 2 B 5
> 2 D 1
> 3 A 6
> 3 C 2
> 3 D 7
>
> I have tried 3 days to get my result like but can't
>
> ID A B C D
> 1 4 10 2
> 2 5 1
> 3 6 2 7
>
> I tried to use a select stmt as
>
> SELECT ID, f(A,ID), f(B,ID), f(C,ID), f(D,ID)
> FROM a_table
> WHERE conditon =..
>
> But the problem is when the return ID becomes so huge, that the call of
> function will be saturating the memory stack area. Anyone has better
> suggestion? Very thanks.
>
> C Chang
Received on Fri Feb 01 2002 - 03:15:17 CST