pl/sql challenge

[sumeet22]

4/1/2006 4/2/2006 4/3/2006 Total
John
ModelA 1 2 1 4
ModelB 2 3 4 9

Sara
ModelA 2 2 2 6
ModelB 3 3 1 7

I need to spool result into csv file. The above table did not come out nice in this post. Here is the scenario. John inserted 1, 2, and 1 records of ModelA on dates 4/1, 4/2 and 4/3 respectively. And 2, 3, and 4 for ModelB

Sara inserted inserted 2, 2, and 2 records of ModelA on dates 4/1, 4/2 and 4/3 respectively. And 3, 3, and 1 for ModelB.

Now I need to display this result in a tabular (pivotal format). The last column will be total of each row for ModelA and ModelB for each person.

I received responses from some nice folks in this post but none of the solutions seem to work for my scenario. The number of columns will span to the right depending on the sysdate and I need to display the actual dates as column names. The report will be run by auto sys scheduler and will be mailed to the users as csv attachment. Scheduling and emailing will be taken care of by someone else. However, producing the nice looking format with right data is my headance.

I don't have write permission to the UTL file either. And don't know if that is the right path to take.

Since I am new to plsql I can't seems to figure out the solution.

Please help.
Sum

[tamilselvan]

You need to post the following if you want quick answer.
1. Create table statement
2. insert statements with sample data.
3. Output format required

Tamil

[sumeet22]

Thanks for the reply.

Here is a sample create statement for table

create table test
(Model varchar2(20), Insert_User varchar(8), Insert_Date date)

Here is sample insert statement

insert into test
select 'ModelA', 'John', sysdate-(rownum-1) from all_objects where rownum<=2

insert into test
select 'ModelB', 'John', sysdate-(rownum-1) from all_objects where rownum<=2

insert into test
select 'ModelA', 'Sara', sysdate-(rownum-1) from all_objects where rownum<=2

insert into test
select 'ModelB', 'Sara', sysdate-(rownum-1) from all_objects where rownum<=2

The format I need is as follows:

4/17/2006 4/18/2006 DailyTotal

John
ModelA 1 1 2
ModelB 1 1 2

Sara
ModelA 1 1 2
ModelB 1 1 2

Somehow the format got squeezed. Colums with date headings go to the right side to line up nicely with ModelA and ModelB's counts for each date for each person. Also row totals should line up with Dailytotal column.

In other words it is a pivotal output.

Thanks for your help.
Sum

[tamilselvan]

For John

select mdl,
       max(decode(rn,17,tot,null)) "04/17/06",
       max(decode(rn,18,tot,null)) "04/18/06",
       max(decode(rn,17,tot,null)) +
       max(decode(rn,18,tot,null))  "Total"
from (select  mdl,
        to_number(to_char(idt,'dd')) rn ,
        sum(ct) tot
        from ( select model mdl,
                      insert_date idt,
                      count(*) ct
                  from test
                  where insert_user = 'John'
                  group by  model, insert_date
              )
        group by mdl, idt
)
group by mdl
/

SQL> @p1

MDL                    04/17/06   04/18/06      Total
-------------------- ---------- ---------- ----------
ModelA                        1          1          2
ModelB                        1          1          2

For all users,

SQL> get p1
  1  select usr, mdl,
  2         max(decode(rn,17,tot,null)) "04/17/06",
  3         max(decode(rn,18,tot,null)) "04/18/06",
  4         max(decode(rn,17,tot,null)) +
  5         max(decode(rn,18,tot,null))  "Total"
  6  from (select  usr, mdl,
  7          to_number(to_char(idt,'dd')) rn ,
  8          sum(ct) tot
  9          from ( select insert_user usr, model mdl,
 10                        insert_date idt,
 11                        count(*) ct
 12                    from test
 13                    --where insert_user = 'John'
 14                    group by  insert_user, model, insert_date
 15                )
 16          group by usr, mdl, idt
 17  )
 18* group by usr, mdl
SQL> /

USR      MDL                    04/17/06   04/18/06      Total
-------- -------------------- ---------- ---------- ----------
John     ModelA                        1          1          2
John     ModelB                        1          1          2
Sara     ModelA                        1          1          2
Sara     ModelB                        1          1          2

Tamil

[sumeet22]

Hi Tamil,

Thank you very much for your post. That is exactly what I needed. However, In this example we know how many days (4/17/2996 and 4/18/2006) to query for.

The real problem is, let's say I run the query today, now I have to count for each day from the beginning of that month. Which means for the month of April it will be from 4/1/2006, 4/2/2006.....4/18/2006 (sysdate). The number of columns should dynamically span depending on which day the query is run. Hard coding the column names as "4/1/2006", "4/2/2006" is not the proper solution.

How can this dynamic behavior be achieved ? Or is it even possible ?

Your help is greatly appreciated.
thanks.
Sumeet.

[tamilselvan]

Tom Kyte wrote a package for dynamically pivoting the result set in his book "Expert one-on-one" .

It's worth to buy the book.

Here is the another simple SQL:

 1  select insert_user, model,
  2         sum( case when trunc(insert_date) = to_date('17-APR-2006', 'DD-MON-YYYY')
  3                   then  1
  4              end ) "04/17/2006" ,
  5         sum( case when trunc(insert_date) = to_date('18-APR-2006', 'DD-MON-YYYY')
  6                 then  1
  7             end ) "04/18/2006",
  8         sum( case when trunc(insert_date) = to_date('17-APR-2006', 'DD-MON-YYYY')
  9                   then  1
 10              end )+
 11         sum( case when trunc(insert_date) = to_date('18-APR-2006', 'DD-MON-YYYY')
 12                 then  1
 13             end ) total
 14  from test
 15* group by insert_user, model
 16  /

INSERT_U MODEL                04/17/2006 04/18/2006      TOTAL
-------- -------------------- ---------- ---------- ----------
John     ModelA                        1          1          2
John     ModelB                        1          1          2
Sara     ModelA                        1          1          2
Sara     ModelB                        1          1          2

Tamil
Tamil

[cjard]

4/1/2006 4/2/2006 4/3/2006 Total
...I need to display the actual dates as column names...

you cannot do this.. this is the job of the reporting tool


have you tried a reporting package? it seems to be what you need. oracle reports?