Display the result of a query in column 1

[hpaille]

Hi,

I have a query returning amounts per outlets, per dates (7 dates):

outlet Amount date
1001 12 21/02/05
1001 13 22/02/05
1001 5 23/02/05
1001 0 24/02/05
1001 0 25/02/05
1001 0 26/02/05
1001 0 28/02/05
1002 4 21/02/05
(...)
Now, I woul like to display this result like this:

outlet Amount_date_1 Amount_d_2 Amount_d_3 (...) Amount_d_7
1001 12 13 5 0
1002 4 (...)

Can I do that with a SELECT without using a table ?

Thanks in advance

 

[RhythmAddict112]

your use of "table" in that context makes me think you are not referring to a DB table...Where do you want to display it? ie, HTML? please clarify and im sure someone will be glad to help


Use your resources, you're on the internet!

 

[hpaille]

Yes, it is from a DB table (or the result of a query that can be used as a table). The aim is to produce a text file with the data formatted as explained above.

 

[SantaMufasa]

HPaille,

I believe that this code should take care of business for you (provided that there are truly 7 values for each outlet):

Section 1 -- Sample data:

select * from outlet_amounts;

    OUTLET     AMOUNT AMT_DATE
---------- ---------- ---------
      1001         12 21-FEB-05
      1001         13 22-FEB-05
      1001          5 23-FEB-05
      1001          0 24-FEB-05
      1001          0 25-FEB-05
      1001          0 26-FEB-05
      1001          0 28-FEB-05
      1002         22 21-FEB-05
      1002         23 22-FEB-05
      1002         25 23-FEB-05
      1002         20 24-FEB-05
      1002         20 25-FEB-05
      1002         20 26-FEB-05
      1002         20 28-FEB-05

Section 2 -- SQL SELECT code:

col a heading "Amount|Date 1" format 9,999
col b heading "Amount|Date 2" format 9,999
col c heading "Amount|Date 3" format 9,999
col d heading "Amount|Date 4" format 9,999
col e heading "Amount|Date 5" format 9,999
col f heading "Amount|Date 6" format 9,999
col g heading "Amount|Date 7" format 9,999
select outlet
      ,sum(amt_dt_1)a
      ,sum(amt_dt_2)b
      ,sum(amt_dt_3)c
      ,sum(amt_dt_4)d
      ,sum(amt_dt_5)e
      ,sum(amt_dt_6)f
      ,sum(amt_dt_7)g
from (select outlet
            ,decode(mod(rownum,7),1,amount)amt_dt_1
            ,decode(mod(rownum,7),2,amount)amt_dt_2
            ,decode(mod(rownum,7),3,amount)amt_dt_3
            ,decode(mod(rownum,7),4,amount)amt_dt_4
            ,decode(mod(rownum,7),5,amount)amt_dt_5
            ,decode(mod(rownum,7),6,amount)amt_dt_6
            ,decode(mod(rownum,7),0,amount)amt_dt_7
        from (select outlet,amount,amt_date
                from outlet_amounts
               order by outlet,amt_date
             )
     )
group by outlet
/

           Amount Amount Amount Amount Amount Amount Amount
    OUTLET Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7
---------- ------ ------ ------ ------ ------ ------ ------
      1001     12     13      5      0      0      0      0
      1002     22     23     25     20     20     20     20

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 10:26 (25Feb05) UTC (aka "GMT" and "Zulu"),
@ 03:26 (25Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[hpaille]

Yes, perfect, thank you.