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
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>