Hi Tom,
I am new to SQL and PLSQL, I have a requirement to create a report, which includes dynamic column for month based on number of months from Program duration.
I actually tried to create scripts in LiveSQL, but right now it is not accessible, hence I am giving all the sample script ready to run.
Create table program_det (program_name varchar2(50), program_date NUMBER);
/
Insert into program_det values('PROGRAM_1', 201801);
Insert into program_det values('PROGRAM_1', 201807);
COMMIT;
/
---------------------------------------------------------------------------------------------------
What I exactly meant is
I have columns <Program_Name>, <Program_Date>
Suppose I have one Program duration of 6 months, say from 201801 to 201806, the the total number of month is 6. In this case I need to dynamically generate 6 columns in the report as below.
Program_Name Program_Date Month_1 Month_2 Month_3 Month_4 Month_5 Month_6
Program_1 12018 0 0 0 0 0
Program_1 62018 0 0 0 0 0
---------------------------------------------------------------------------------------------------
I have tried with below approach, here I am able to find the number of months but I need to help to create dynamic columns based on this number of months.
select program_name,
months_between(TO_DATE(max(program_date),'YYYYMM'),TO_DATE(min(program_date),'YYYYMM'))
number_months
from program_det
where program_name ='PROGRAM_1'
group by program_name;
---------------------------------------------------------------------------------------------------
I am not sure, if this may be very silly and simple, but I am unable to find a solution.
Please help!
Regards,
Kavya
The challenge here is that you need to do a *dynamic* pivot, ie, the number of columns is unknown until you look at the data. Stew Ashton wrote up a nice blog post on how to do that here
https://stewashton.wordpress.com/2018/05/28/generic-pivot-function/ which I'll use to help with this question. I've also used DATE datatypes because NEVER EVER use numbers for data.
First I can generate the months I need
SQL> Create table program_det (program_name varchar2(50), program_date date);
Table created.
SQL> Insert into program_det values('PROGRAM_1', date '2018-01-01');
1 row created.
SQL> Insert into program_det values('PROGRAM_1', date '2018-07-01');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> with pgm as
2 ( select program_name,
3 min(program_date) mind,
4 max(program_date) maxd
5 from program_det
6 group by program_name )
7 select *
8 from pgm p,
9 table(cast(multiset(
10 select add_months(p.mind,rownum-1)
11 from pgm
12 where program_name = p.program_name
13 connect by add_months(mind,rownum-1) <= p.maxd
14 ) as sys.odcidatelist));
PROGRAM_NAME MIND MAXD COLUMN_VA
-------------------------------------------------- --------- --------- ---------
PROGRAM_1 01-JAN-18 01-JUL-18 01-JAN-18
PROGRAM_1 01-JAN-18 01-JUL-18 01-FEB-18
PROGRAM_1 01-JAN-18 01-JUL-18 01-MAR-18
PROGRAM_1 01-JAN-18 01-JUL-18 01-APR-18
PROGRAM_1 01-JAN-18 01-JUL-18 01-MAY-18
PROGRAM_1 01-JAN-18 01-JUL-18 01-JUN-18
PROGRAM_1 01-JAN-18 01-JUL-18 01-JUL-18
7 rows selected.
Now just for ease of readability I'll create a view on that
SQL> create or replace view v as
2 with pgm as
3 ( select program_name,
4 min(program_date) mind,
5 max(program_date) maxd
6 from program_det
7 group by program_name )
8 select program_name,
9 to_char(column_value,'YYYYMM') mth
10 from pgm p,
11 table(cast(multiset(
12 select add_months(p.mind,rownum-1)
13 from pgm
14 where program_name = p.program_name
15 connect by add_months(mind,rownum-1) <= p.maxd
16 ) as sys.odcidatelist));
View created.
and then I'll pass it into the generic pivot
SQL> set serveroutput off
SQL> var rc refcursor
SQL> begin
2 :rc := generic_pivot(
3 '(select program_name, mth, 0 val from v)',
4 'MTH',
5 'SUM(VAL)'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> print rc
PROGRAM_NAME 201801 201802 201803 201804 201805 201806 201807
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
PROGRAM_1
I've hard-coded zero in there, but you would join or similar to get the numeric data you want to present.