i have to convert horizontal rows to column table. for example, assume i have a table with 10 columns. i want to convert this to 2 columns(1 column will hold the column name and another column will hald the value) using unpivot. i tried with with_clause(to get aggrgate columns from all_tab_columns) and unpivot but giving error simple column error or in clause error.Could someone suggest where i am getting error. or another approach.
input table
KEY_COL COL_A COL_B COL_C..till COL_J
1 a,b,c,d,e,f,g,h,i,j
2 x,y,a,s,f,g,e,r,g,d
output table
KEY_COL COLM VALUE
1 COL_A a
1 COL_B b
.
.
.
1 COL_J j
2 COL_A x
2 COL_B y
.
.
.
2 COL_J d
i am able to do for one table with hard coded in_clause in unpivot section. but i need to do same for many tables. so i looking for dynamic solution.