colums to rows transpose

[christiniaroelin]

Hi
I have the query that returns the following data:

CUST_ID DT SUM(Scrpt_CNT)
---------------- --------- ---------------------
901 28-OCT-05 0
903 07-OCT-05 0
903 14-OCT-05 0
903 21-OCT-05 0
903 28-OCT-05 0
905 07-OCT-05 21
905 14-OCT-05 24
905 21-OCT-05 26
905 28-OCT-05 35
906 07-OCT-05 0
906 14-OCT-05 0


I would like the output to be the following:

cust_id date sum date sum date sum
901 28-OCT-05 0
903 07-OCT-05 0 14-OCT-05 0 21-OCT-05 0
....
....

basically transposing columns to rows for date and sum for each distinct cutomer id.

Please help!!

Thanks for your time and help!!
Chtistinia

 

[SantaMufasa]

Christina,

We need a few clarifications:
[tt]
1) What is the venue for your output?...Is it for
a) display to the screen
b) spool to a client-side flat file
c) server-side flat file
d) output for a VIEW
e) output to a table

2) If the output is a-c, above, do you want
a) fixed-length columns
b) delimited columns, delimited by:
i) commas
ii) tabs
iii) some other character

3) What is the maximum number of transposed rows-to-columns for a single CUST_ID?
a) known
b) unknown/unlimited
[/tt]

Your responses to the above questions significantly affect the resolution that we suggest to you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[christiniaroelin]

Mufasa,

What is the venue for your output?...Is it for
- display to the screen


If the output is a-c, above, do you want
delimited columns, delimited by:
- tabs

What is the maximum number of transposed rows-to-columns for a single CUST_ID
- unknown. This could be anywhere between a specfied range of dates.

Thanks for your time.

regards,
christiana

 

[SantaMufasa]

Christiana,

Sorry for the delay. (...I was out trying to get my Christmas shopping done early .)

Since your output venue is display, it simplifies things quite a bit. I believe that the simplest method to achieve what you want is with a user-defined function:

create or replace function get_cust_scrpt_sum (cust_in number)
    return varchar2
is
    sep_str     varchar2(10) := chr(9); -- <tab> chr
    hold_str    varchar2(2000);
begin
    for c in (select dt,sum(scrpt_cnt) sum_scrpt
                from cust_scrpt
               where cust_id = cust_in
               group by dt) loop
        hold_str := hold_str||sep_str||c.dt||sep_str||c.sum_scrpt;
    end loop;
    return ltrim(hold_str,sep_str);
end;
/

Function created.

set linesize 150
col a heading "Formatted output" format a100
select distinct cust_id, get_cust_scrpt_sum(cust_id) a
from cust_scrpt
order by cust_id;

   CUST_ID Formatted output
---------- ---------------------------------------------------------------------------------------
       901 28-OCT-05    0
       903 07-OCT-05    0       14-OCT-05       0       21-OCT-05       0       28-OCT-05       0
       905 07-OCT-05    21      14-OCT-05       24      21-OCT-05       26      28-OCT-05       35
       906 07-OCT-05    0       14-OCT-05       0
**************************************************************************************************

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.