Write Oracle proc/function to create dynamic views with changing column headers

Question

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:

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.

P.S. Application at the end is Oracle apex from where the proc/function would be called.

Oracle Version: 12.1

Answer 1

Why does it have to be one query?

Why not retrieve the column headers in one step, pull the real data in another and then stitch the two together, if that's even required?

If this is being done under application control (as opposed to a single database "query"), you may not need to do this "reassembly" at all. It might be possible to populate your column headers separately from the real data.