Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with select statement (pivot table?)
ak_tiredofspam_at_yahoo.com wrote:
> create table a(n number, n1 number, n2 number)
> Table created
>
> insert into a values(1, 11, 12)
> 1 row inserted
>
>
> insert into a values(2, 21, 22)
> 1 row inserted
>
>
> select n, k,
> case when k=1 then n1 else n2 end n_pivoted
> from a,
> (select 1 k from dual union all select 2 k from dual)
> N K N_PIVOTED
> ---------- ---------- ----------
> 1 1 11
> 2 1 21
> 1 2 12
> 2 2 22
> 4 rows selected
>
>
> drop table a
> Table dropped
>
> HIH
OK. I have the first step done. I'm able to unpivot the table by the
following statement.
select year, account, rownum, decode(rownum, 1, Period1, 2, Period2, 3, Period3, 4, Period4, 5, Period5, 6, Period6, 7, Period7, 8, Period8, 9, Period9, 10, Period10, 11, Period11, 12, Period12) from table1, (select rownum from all_objects where rownum <=12)
I am having a hard time summing the periods.
select year, account, rownum, sum(decode(rownum, 1, Period1, 2, Period2, 3, Period3, 4, Period4, 5, Period5, 6, Period6, 7, Period7, 8, Period8, 9, Period9, 10, Period10, 11, Period11, 12, Period12)) from table1, (select rownum from all_objects where rownum <=12) group by year, account
This doesn't seem to work. I just get a null value. Could it have something to do with selecting from the all_objects table?
Thanks... AL Received on Tue May 03 2005 - 11:46:36 CDT