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.
P.S. Application at the end is Oracle apex from where the proc/function would be called.
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:
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