pivot sample

[kris123]

Hi Friends,

I have a travel table ARRIVAL_STATS
(port_name varchar2(10),
arrival_date date,
count1 number)

sample values:

PORT01, 01-JAN-05, 10000
PORT01, 02-JAN-05, 12000

I want to print a report that looks like this:

ARRIVAL STATS FOR 2005
PORTNAME: PORT01


MONTH/
DAY JAN FEB MAR APR MAY ....... DEC
1 1000 1000 ... ... ... .......
2 1000 1000
3 1000 1000
4 1000 1000
5 1000 1000
6 1000 1100
7 1000 1100
8 1000 1100
9 2000 1100
10 2000 1100
11 2000 1200
12 2000 1200
13 2000 1200
14 2000 1200
15 2000 1200
16 2000 1300
17 2000 1300
18 2000 1300
19 2000 1300
20 2000 1300
21 3000 1300
22 3000 1400
23 3000 1400
24 3000 1400
25 3000 1400
26 3000 1400
27 3000 1400
28 3000 1400
29 3000 -
30 3000 -
31 3000 -

The report should display a fixed spreadsheet of 12x31 for month and day. If count for a certain day is not available it will display "-".

Can u help me pls...

Thanks a lot

[jmodic]

select port_name,
       to_char(arrival_date, 'yyyy') year,
       to_char(arrival_date, 'dd') day,
       sum(decode(to_char(arrival_date, 'MON'), 'JAN', 1, 0) JAN,
       sum(decode(to_char(arrival_date, 'MON'), 'FEB', 1, 0) FEB,
       ....
       sum(decode(to_char(arrival_date, 'MON'), 'DEC', 1, 0) DEC
  from arrival_stats
 group by port_name, to_char(arrival_date, 'yyyy'), to_char(arrival_date, 'dd');

[kris123]

Thanks hun, your always here(like Clark Kent )...just when i needed you most

[kris123]

Hi Again Friends,

I tried to used BREAK ON COLUMN to display PORT NAME and YEAR once
for every group change, but it does not work. It still display similar
port and year many times. I issue the following commnad:

sql> BREAK ON PORT_NAME,YEAR

(then run the progam)

Is there something wrong with this break command?

I issued BREAK ON PORT_NAME and it worked, but i want to have
break on 2 columns. Can you help again pls...

Thanks a lot

[jmodic]

BREAK ON PORTNAME ON YEAR

[kris123]

lol...how silly i can be (i wonder why it didnt give me error
when using BREAK ON REPORT_NAME,YEAR)

Thanks again dear

[kris123]

hi again friend,

I found out that the report is not fixed on 12x31 when i only have
1 row test data. How can I display all 31 days even if there is no
data in it?

Thanks again

[tamilselvan]

Just create a small table that has 31 rows.

SQL> desc just_num
 Name                       Null?    Type
 -------------------------- -------- ------------------
 RN                                  NUMBER

SQL> select * from cube_ex ;

PORT_NAME  ARRIVAL_D     COUNT1
---------- --------- ----------
PORT01     24_FEB-05         10

Your SQL should be :

select a.rn , b.*
from
       just_num a
       left outer join
       (select port_name,
       to_char(arrival_date, 'yyyy') year,
       to_number(to_char(arrival_date, 'dd')) day,
       sum(decode(to_char(arrival_date, 'MON'), 'JAN', 1, 0)) JAN,
       sum(decode(to_char(arrival_date, 'MON'), 'FEB', 1, 0)) FEB,
       sum(decode(to_char(arrival_date, 'MON'), 'MAR', 1, 0)) MAR
       from    cube_ex
       group  by port_name, to_char(arrival_date, 'yyyy'), to_char(arrival_date, 'dd')) B
on     a.rn = b.day
order by a.rn
;

SQL> /

        RN PORT_NAME  YEAR        DAY        JAN        FEB        MAR
---------- ---------- ---- ---------- ---------- ---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24 PORT01     2005         24          0          1          0
        25
        26
        27
        28
        29
        30
        31

31 rows selected. 

I am trying to use cube function (not for generating 31 rows) for the main SQL to arrive a simple solution.

Tamil

[kris123]

thanks a lot friends

[kris123]

hi again friends,

I want to display the heading(PORT_NAME YEAR DAY JAN FEB MAR ....)
on every break on year, and should be printed on a new page(1 page report per year). Right now the headings appear on default like every 20 rows selected. Is there a command BREAK ON YEAR ON HEADING?

Thanks a lot