This question is most common with no clear solution.
I have 2 scenarios for dynamic pivoting and the solution requested could be using pivot xml(tried, but extracting is a task)/dynamic sql execution/arays if possible we could use..
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
ID label value app_id
--- ----- ----- ------
120 A Alpha 1
120 B Beta 1
120 C Class 1
120 D Delta 1
120 C Alpha 2
120 D Beta 2
120 E Class 2
120 F Delta 2
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.
SELECT * FROM data WHERE ID = 120 AND app_id = 1;
app_id A B C D ID
------ ------ ----- ----- ----- -----
1 Alpha Beta Class Delta 120
SELECT * FROM data WHERE ID = 120 AND app_id = 2;
app_id C D E F ID
------ ------ ----- ----- ----- -----
2 Alpha Beta Class Delta 120
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:
APP_ID SEQ HEADER_NAME
1 1 A
1 2 B
1 3 C
1 4 D
1 5 E
1 6 F
1 7 G
1 8 H
1 9 I
1 10 J
2 1 P
2 2 Q
2 3 R
2 4 S
2 5 T
2 6 U
2 7 V
2 8 W
2 9 X
2 10 Y
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.