I have 2 scenarios for dynamic pivoting.
Scenario 1:
In the first case,I i need to pivot the data where data source is same table. However the pivot needs to be dynamic as the columns header would keep changing as per column app_id. So if app_id=1. The column header would be A,B,C,D, If app_id=2, column would be CDEF and so on. Also each set of value has an id. So for id, 120 and app_id=1 , column A,B,C,D, would display the values and so on.
The current sample data has only 2 app_ids, but there could be many more, so app_id and labels would kepe changing thus i need to write a dynamic query.
Table is DATA_1
And expected output would be something like this.
P.S. Data is dummy and app_ids, and ids would keep getting inserted and removed with changing column headers,so a dynamic solution is needed.
Scenario 2:
Here were had the data in one table. Now we have separate table containing the labels and current table with values.
SO in table from scenario one we will focus on id, app_id and value columns only.
Select app_id,id,value from data_1;
The labels will come from another table DATA_Labels in column Header_Name:
So, for labels we would use header_names. ANd Data_1 and Data_Labels could be joined on basis of app_id.
In case the values exceed column headers, we can assume that the headers would reach say maximum 20. And in case the header/name is not available, it could put some default header name like COL11,COL12 if the value stretches upto there.
I did lot of research but most solutions are too complex and confusing. Any leads would be appreciated.
The solution could be with/without pivot or we can also use collections/arrays if possible. ANything that helps in changing the headers dynamically.