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

MarkyG wrote:
>
> 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

Thanks Mark. However, I am not sure this is practical. Not only it uses an run-time temp table, but there are 10000 ( 10000 IDs) inserts to create it, my client probably does not have time to wait it to finish. I will keep looking an efficient way. Actually, I am thinking a select procedure like this:
SELECT ID, f(ID) from my_table ..

where the f(ID) is a function return VARCHAR2, in which the return string is as A/4;B/10;C/2 .... But the problem is how to return an array in order of A,B,C even some of missing like ID=3. Thanks anyway.

C Chang Received on Fri Feb 01 2002 - 22:14:32 CST