Skip to Main Content
  • Questions
  • Oracle PL/SQL Procedure/function to dynamically create view with dynamic columns

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 24, 2020 - 10:11 am UTC

Last updated: November 25, 2020 - 5:16 am UTC

Version: Oracle 12c

Viewed 100+ times

You Asked

I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.

I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view.

***The entire sample data and structure is available here:***

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b

The table with column headers is DATA_HEADER.

The table with value is DATA_VALUE.

The column headers and values need to be pivoted in order.

Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id.

When app_id is passed in proc/funct, the expected view should be:

PID Title Group Aspect EPT IT BU Section Class Label Value Options Comments
120 Robert Deputy IT Synopsis is good Standard Code Green Needs Improvement for the details
120 Stanley Deputy IT Synopsis is good Standard Code Green Needs Improvement for the details Average

call proc(app_id=>2)
PID Project Idea Perspective Analysis Status Dept Aspect 3 Class Label Value Options Comments Remarks
120 Robert Deputy IT Synopsis is good Standard Code Green Needs Improvement for the details
121 Stanley Deputy IT Synopsis is good Standard Code Green Needs Improvement for the details Average

(Apologies for the structure but i did not know how to format a table,hope the expected result is clear)

So basically, the headers change for each app_id, and the max number of column headers will be 20. So the number or name shall vary as in the table DATA_HEADER.
The values are uniquely identified on the basis of pid.

The order of column headers would be as per the seq column in DATA_HEADER.
Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly.

Oracle Version: 12.1

and we said...

This should get you started


SQL>
SQL> create table data_header(app_id number,label varchar2(400),seq number);

Table created.

SQL>
SQL> INSERT ALL
  2  INTO DATA_HEADER VALUES(1,'Title',1)
  3  INTO DATA_HEADER VALUES(1,'Group',2)
  4  INTO DATA_HEADER VALUES(1,'Aspect',3)
  5  INTO DATA_HEADER VALUES(1,'EPT',4)
  6  INTO DATA_HEADER VALUES(1,'IT',5)
  7  INTO DATA_HEADER VALUES(1,'BU',6)
  8  INTO DATA_HEADER VALUES(1,'Section',7)
  9  INTO DATA_HEADER VALUES(1,'Class',8)
 10  INTO DATA_HEADER VALUES(1,'Label Value Options',9)
 11  INTO DATA_HEADER VALUES(1,'Comments',10)
 12  INTO DATA_HEADER VALUES(2,'Title',1)
 13  INTO DATA_HEADER VALUES(2,'Group',2)
 14  INTO DATA_HEADER VALUES(2,'Aspect',3)
 15  INTO DATA_HEADER VALUES(2,'EPT',4)
 16  INTO DATA_HEADER VALUES(2,'IT',5)
 17  INTO DATA_HEADER VALUES(2,'BU',6)
 18  INTO DATA_HEADER VALUES(2,'Section',7)
 19  INTO DATA_HEADER VALUES(2,'Class',8)
 20  INTO DATA_HEADER VALUES(2,'Label Value Options',9)
 21  INTO DATA_HEADER VALUES(2,'Comments',10)
 22  INTO DATA_HEADER VALUES(2,'Remarks',11)
 23  select * from dual;

21 rows created.

SQL>
SQL> create table data_value(pid number,value varchar2(4000),app_id number,seq number);

Table created.

SQL>
SQL> INSERT ALL INTO DATA_VALUE VALUES(120,'Robert',1,1)
  2  INTO DATA_VALUE VALUES(120,'Deputy',1,2)
  3  INTO DATA_VALUE VALUES(120,'IT',1,3)
  4  INTO DATA_VALUE VALUES(120,'Synopsis is good',1,4)
  5  INTO DATA_VALUE VALUES(120,'Standard',1,5)
  6  INTO DATA_VALUE VALUES(120,'Code',1,6)
  7  INTO DATA_VALUE VALUES(120,'Green',1,7)
  8  INTO DATA_VALUE VALUES(120,'Needs Improvement for the details ',1,8)
  9  INTO DATA_VALUE VALUES(120,'Robert',2,1)
 10  INTO DATA_VALUE VALUES(120,'Deputy',2,2)
 11  INTO DATA_VALUE VALUES(120,'IT',2,3)
 12  INTO DATA_VALUE VALUES(120,'Synopsis is good',2,4)
 13  INTO DATA_VALUE VALUES(120,'Standard',2,5)
 14  INTO DATA_VALUE VALUES(120,'Code',2,6)
 15  INTO DATA_VALUE VALUES(120,'Green',2,7)
 16  INTO DATA_VALUE VALUES(120,'Needs Improvement for the details ',2,8)
 17  INTO DATA_VALUE VALUES(121,'Stanley',1,1)
 18  INTO DATA_VALUE VALUES(121,'Deputy',1,2)
 19  INTO DATA_VALUE VALUES(121,'IT',1,3)
 20  INTO DATA_VALUE VALUES(121,'Synopsis is good',1,4)
 21  INTO DATA_VALUE VALUES(121,'Standard',1,5)
 22  INTO DATA_VALUE VALUES(121,'Code',1,6)
 23  INTO DATA_VALUE VALUES(121,'Green',1,7)
 24  INTO DATA_VALUE VALUES(121,'Needs Improvement for the details ',1,8)
 25  INTO DATA_VALUE VALUES(121,'Average',1,9)
 26  INTO DATA_VALUE VALUES(121,'Stanley',2,1)
 27  INTO DATA_VALUE VALUES(121,'Deputy',2,2)
 28  INTO DATA_VALUE VALUES(121,'IT',2,3)
 29  INTO DATA_VALUE VALUES(121,'Synopsis is good',2,4)
 30  INTO DATA_VALUE VALUES(121,'Standard',2,5)
 31  INTO DATA_VALUE VALUES(121,'Code',2,6)
 32  INTO DATA_VALUE VALUES(121,'Green',2,7)
 33  INTO DATA_VALUE VALUES(121,'Needs Improvement for the details ',2,8)
 34  INTO DATA_VALUE VALUES(121,'Average',2,9)
 35  SELECT * FROM DUAL;

34 rows created.

SQL>
SQL> set serverout on
SQL> declare
  2    l_view_header clob;
  3    l_view_cols   clob;
  4    l_seq_hwm     int;
  5  begin
  6
  7    select 'create or replace view MY_VIEW ( '|| listagg('"'||label||'"',',') within group ( order by seq ) || ')',
  8           max(seq)
  9    into   l_view_header,
 10           l_seq_hwm
 11    from   data_header
 12    where  app_id = 1;
 13
 14    select ' as select pid, '||listagg('max(decode(seq,'||rownum||',value))'||chr(10),',') within group ( order by level )
 15    into l_view_cols
 16    from   dual
 17    connect by level <= l_seq_hwm;
 18
 19    l_view_cols := l_view_cols || ' from DATA_VALUE where app_id = 1 group by pid';
 20
 21    dbms_output.put_line(l_view_header);
 22    dbms_output.put_line(l_view_cols);
 23  end;
 24  /
create or replace view MY_VIEW ( "Title","Group","Aspect","EPT","IT","BU","Section","Class","Label Value Options","Comments")
as select pid,
max(decode(seq,1,value))
,max(decode(seq,2,value))
,max(decode(seq,3,value))
,max(decode(seq,4,value))
,max(decode(seq,5,value))
,max(decode(seq,6,value))
,max(decode(seq,7,value))
,max(decode(seq,8,v
alue))
,max(decode(seq,9,value))
,max(decode(seq,10,value))
 from DATA_VALUE where app_id = 1 group by pid

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.