Recently I had a peculiar requirement in my project of converting columns into rows. For simplicity sake, I will take small subset . .
Suppose we have a table ,
A B C
a 1 2
b 3 4
and you want display to be
There can be different approaches to achieve this.
a) If it’s a small data set it can be achieved by UNION.
b) If data set is large, we can use Collections such as nested tables.
c) And there is one more efficient way to do this: A simple joining with data dictionary and using decode on column number, easily solve the issue and also if you check the query timings it is faster than other two methods.
Select * from
( select A, Decode ( column_id,2,b,3,c)
From mytab,user_tab_columns where table_name=’MYTAB’);