Our environment is
Database 10g 10.2.0.1.0, Forms 6i
Operating System: Windows Server 2008R2
We have tables to record schedule for our distributors like following
create table distributor ( dcode int, distributor_name varchar2 (200));
create table loadplan (mnth_no number, year_no number, dcode int, day_no int);
with data in it:
insert into distributor values ( 1, 'DAVID');
insert into distributor values ( 2, 'TAHIR');
insert into distributor values ( 3, 'ABBOTT');
insert into loadplan values ( 10, 2017, 1, 1);
insert into loadplan values ( 10, 2017, 1, 11);
insert into loadplan values ( 10, 2017, 1, 22);
insert into loadplan values ( 10, 2017, 2, 2);
insert into loadplan values ( 10, 2017, 2, 12);
insert into loadplan values ( 10, 2017, 2, 24);
insert into loadplan values ( 10, 2017, 3, 3);
insert into loadplan values ( 10, 2017, 3, 13);
insert into loadplan values ( 10, 2017, 3, 26);
insert into loadplan values ( 11, 2017, 1, 1);
insert into loadplan values ( 11, 2017, 1, 11);
insert into loadplan values ( 11, 2017, 1, 22);
insert into loadplan values ( 11, 2017, 2, 2);
insert into loadplan values ( 11, 2017, 2, 12);
insert into loadplan values ( 11, 2017, 2, 24);
insert into loadplan values ( 11, 2017, 3, 3);
insert into loadplan values ( 11, 2017, 3, 13);
insert into loadplan values ( 11, 2017, 3, 26);
We need to develop a query so that we get monthly sheet with daywise schedule of all distributors
SNo Distributor_Name Month_No Year_No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
I'm not sure exactly what you're wanting to show in the day columns?
In any case, you want to pivot. Which from 11g up is as easy as:
select * from loadplan
pivot (
count(*) for day_no in (
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)
);
MNTH_NO YEAR_NO DCODE 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
10 2017 1 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
10 2017 2 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
10 2017 3 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0
11 2017 1 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
11 2017 2 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
11 2017 3 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0Replace count(*) with whatever you want to show in the day columns. Note this must be an aggregate (min/avg/etc.) function!
Sadly you're on 10g.
So you'll have to do it the old-school manual way. Group by month/year/etc. Then repeat the aggregate for each day. Inside this have a check if the current day value equals the day that column represents. If it does, return a value. If not null.
For example:
select mnth_no, year_no, dcode,
count(case when day_no = 1 then 1 end) d1,
count(case when day_no = 2 then 1 end) d2,
count(case when day_no = 3 then 1 end) d3,
count(case when day_no = 4 then 1 end) d4
-- etc.
from loadplan
group by mnth_no, year_no, dcode;
MNTH_NO YEAR_NO DCODE D1 D2 D3 D4
11 2017 1 1 0 0 0
10 2017 1 1 0 0 0
11 2017 2 0 1 0 0
11 2017 3 0 0 1 0
10 2017 3 0 0 1 0
10 2017 2 0 1 0 0 If you want to know more about pivoting, read:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#unpivot