The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, srinath.

Asked: August 30, 2011 - 10:17 am UTC

Last updated: September 03, 2011 - 11:10 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

I have a table pm_project which contains following columns... project_id,project_name,project_start_date,project_end_date... I want to generaate a report where user will select the range of months, I have to display a graph with x axis as the range of months he has selected.. and project_name as Y axis. My query should help plotting this graph with the projects and its range(duration between project_start_date and project_end_date if the project has started before query end date (which user selects) and project should not have ended before query start date ) in the selected period. I need a query to dynamically select project names as alias for result calculated using start date and end date.

I've been trying this query but after the keyword as, if I write a sub query, it is throwing an error.

 declare
TYPE number_table_type IS TABLE OF Varchar2(3260) ;
v number_table_type :=number_table_type(100);
lm varchar2(4400);
ln varchar2(4000);
v_l Number(20) ;
vl varchar2(30);
begin
v.extend(100);
vl:='dhl';
lm :='select (to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''MON-YYYY'')) label';
v(1):=',case when project_id=10053 and (to_char(project_start_date,''yyyymm'') <= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm'') and 
to_char(project_end_date,''yyyymm'')>= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm''))  then 1
else null
end as proj';

ln :=' from pm_project,(select rownum-1 r from all_objects where rownum <= ( select 
max(months_between(:P97_QEDATE,:P97_QSDATE))+1 from pm_project) )
where months_between(:P97_QEDATE,:P97_QSDATE)+1 >= r and project_id=10053' ;

return lm||v(1)||ln;
end;

Here i have tried for one project with column alias hardcoded as "proj".. 'I want alias to be the project name and also i want it for all the project as i have written query for 1 project.. how can i implement the same query for all project with project name as alias'.. Thank you for your response..

------------------------------------------------------------------------------------------------------------

Hi Tom, This is my requiremnet.. I have a table pm_project as below:

create table pm_project(project_id NUMBER not null,
project_name VARCHAR2(40),
project_start_date DATE,
project_end_date DATE);

insert into pm_project values(1001,'DHL','12-mar-2011','14-sep-2011');
insert into pm_project values(1000,'LOWES','10-oct-2010','14-apr-2011');
insert into pm_project values(1002,'XPEDEX','12-mar-2010','14-nov-2010');
insert into pm_project values(1003,'BELRON','12-oct-2011','');
insert into pm_project values(1004,'ACADEMICS','12-june-2010','');

If project_end_date is null , it means project is still in progress.. Now user will select the range of month to see which project was active in that duration(project should have started before query end date and project should not have ended before query start date .. )

lets say user select q_start_date(Query start date) as 12-jan-2011 and q_end_date(query end date) as 18-oct-2011..

I need following output

label DHL LOWES BELRON ACADEMICS
----- --- ----- ------ --------
jan-2011 2 4
feb-2011 2 4
mar-2011 1 2 4
apr-2011 1 2 4
may-2011 1 4
jun-2011 1 4
jul-2011 1 4
aug-2011 1 4
sep-2011 1 4
oct-2011 3 4

If the project is active for that month, its given a value as shown above( Different values are given for different project so we can plot it in a graph)... I tried the following plsql code which returns a sql query and am able to get data for 1 project ..

 declare
TYPE number_table_type IS TABLE OF Varchar2(3260) ;
v number_table_type :=number_table_type(100);
lm varchar2(4400);
ln varchar2(4000);
begin
v.extend(100);

lm :='select (to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''MON-YYYY'')) label';
v(1):=',case when project_id=1000 and (to_char(project_start_date,''yyyymm'') <= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm'') and 
to_char(project_end_date,''yyyymm'')>= to_char(add_months((to_date(:P97_QSDATE,''dd-mm-yyyy'')),r),''yyyymm''))  then 1
else null
end as proj';

ln :=' from pm_project,(select rownum-1 r from all_objects where rownum <= ( select 
max(months_between(:P97_QEDATE,:P97_QSDATE))+1 from pm_project) )
where months_between(:P97_QEDATE,:P97_QSDATE)+1 >= r and project_id=1000' ;

return lm||v(1)||ln;
end;

and here is my output

label proj
----- ----
jan-2011
feb-2011
mar-2011 1
apr-2011 1
may-2011 1
jun-2011 1
jul-2011 1
aug-2011 1
sep-2011 1
oct-2011

But column alias in this output is proj, how can i get the column alias to be the project_name(DHL in this case) dynamically..

Also i need this query to be done for all the projects .. How can i implement it??

and Tom said...

so, first we'll start by figuring out the template query we need:

ops$tkyte%ORA11GR2> variable sdate varchar2(20)
ops$tkyte%ORA11GR2> variable edate varchar2(20)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :sdate := '12-jan-2011'; :edate := '18-oct-2011';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with dates
  2  as
  3  (select add_months( trunc( to_date(:sdate,'dd-mon-yyyy'),'mm' ), level-1 ) dt
  4     from dual
  5  connect by level <= months_between( trunc(to_date(:edate,'dd-mon-yyyy'),'mm'), trunc( to_date(:sdate,'dd-mon-yyyy'),'mm') )+1
  6  ),
  7  p
  8  as
  9  (select *
 10     from pm_project
 11    where ( to_date(:sdate,'dd-mon-yyyy') <= nvl(project_end_date,to_date(:sdate,'dd-mon-yyyy')) )
 12      and ( last_day(to_date(:edate,'dd-mon-yyyy')) >= project_start_date )
 13  )
 14  select dates.dt,
 15         max( decode( project_name, 'DHL', 1 ) ) "DHL",
 16         max( decode( project_name, 'LOWES', 2 ) ) "LOWES",
 17         max( decode( project_name, 'BELRON', 3 ) ) "BELRON",
 18         max( decode( project_name, 'ACADEMICS', 4 ) ) "ACADEMICS"
 19    from dates, p
 20   where dates.dt between trunc(p.project_start_date,'mm') and last_day( nvl(p.project_end_date,dates.dt) )
 21   group by dates.dt
 22   order by dates.dt
 23  /

DT               DHL      LOWES     BELRON  ACADEMICS
--------- ---------- ---------- ---------- ----------
01-JAN-11                     2                     4
01-FEB-11                     2                     4
01-MAR-11          1          2                     4
01-APR-11          1          2                     4
01-MAY-11          1                                4
01-JUN-11          1                                4
01-JUL-11          1                                4
01-AUG-11          1                                4
01-SEP-11          1                                4
01-OCT-11                                3          4

10 rows selected.

so, that gets our result set for us - but since the number and names of the columns we retrieve will change from run to run - we KNOW we cannot do this in a single query - we'll need to use a bit of dynamic sql to build some set of queries for us.

Now that we have the template query - making this into a procedure that returns a result set for us is very very easy:

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure my_pivot( p_sdate in date, p_edate in date, p_query in out sys_refcursor )
  2  as
  3      l_sql long := q'|
  4  with dates
  5  as
  6  (select add_months( trunc( :p_sdate,'mm' ), level-1 ) dt
  7     from dual
  8  connect by level <= months_between( trunc(:p_edate,'mm'), trunc( :p_sdate,'mm') )+1
  9  ),
 10  p
 11  as
 12  (select *
 13     from pm_project
 14    where ( :p_sdate <= nvl(project_end_date,:p_sdate) )
 15      and ( last_day(:p_edate) >= project_start_date )
 16  )
 17  select dates.dt|';
 18  begin
 19      for x in ( select project_name, rownum r
 20                   from (select distinct project_name
 21                              from pm_project
 22                            where ( p_sdate <= nvl(project_end_date,p_sdate) )
 23                            and ( last_day(p_edate) >= project_start_date )
 24                          order by project_name) )
 25      loop
 26          l_sql := l_sql ||
 27                   ', max( decode( project_name, ' ||
 28                   dbms_assert.enquote_literal(x.project_name) ||
 29                   ', ' || x.r || ') )' ||
 30                   dbms_assert.simple_sql_name( '"'|| x.project_name || '"' );
 31      end loop;
 32      l_sql := l_sql || q'|
 33    from dates, p
 34   where dates.dt between trunc(p.project_start_date,'mm') and last_day( nvl(p.project_end_date,dates.dt) )
 35   group by dates.dt
 36   order by dates.dt|';
 37  
 38      open p_query for l_sql using p_sdate, p_edate, p_sdate, p_sdate , p_sdate, p_edate;
 39  
 40  end;
 41  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable q refcursor
ops$tkyte%ORA11GR2> exec my_pivot( to_date(:sdate,'dd-mon-yyyy'), to_date(:edate,'dd-mon-yyyy'), :q )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print q

DT         ACADEMICS     BELRON        DHL      LOWES
--------- ---------- ---------- ---------- ----------
01-JAN-11          1                                4
01-FEB-11          1                                4
01-MAR-11          1                     3          4
01-APR-11          1                     3          4
01-MAY-11          1                     3
01-JUN-11          1                     3
01-JUL-11          1                     3
01-AUG-11          1                     3
01-SEP-11          1                     3
01-OCT-11          1          2

10 rows selected.

And there you go - instant "dynamic pivot"

Rating

(4 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Brilliant

Srinath, September 02, 2011 - 1:48 pm UTC

Tom,
Thanks a lot for your response..Really helpful.. How can we implement dynamic column alias in sql?

September 03, 2011 - 11:06 am UTC

you cannot - the number and names and types of columns must be known at parse time, if the aliases (names) change - then you need a new query.

hence the plsql code demonstrated above.

Same query in a plsql function

Srinath, September 02, 2011 - 2:21 pm UTC

Tom,
I need the same query in a plsql function returning a sql query which can be used to generate a chart.. how can i do the same in Function returning a sql query??

September 03, 2011 - 11:07 am UTC

define what it means to you to return a query. I returned a ref cursor which is in fact "returning a query result set"

dynamic alias and pivot

A reader, September 02, 2011 - 11:00 pm UTC

Thanks Tom, I could implement it as function returning sql query.. Thanks a lot for helping us.. Is there a way to implement dynamic column alias in sql?

September 03, 2011 - 11:10 am UTC

see above, no

Helpful

Srinath, September 03, 2011 - 12:14 pm UTC

oh sorry, i didn't notice your previous followup.. Thanks a lot for solving our problems.. Continue your good work. Thank you.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Brilliant

Same query in a plsql function

dynamic alias and pivot

Helpful

More to Explore

PL/SQL demos

PL/SQL docs

Selecting dynamic alias in plsql

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Brilliant

Same query in a plsql function

dynamic alias and pivot

Helpful

More to Explore

PL/SQL demos

PL/SQL docs