I am looking for the output like this ( around 50 columns in a table)
empno ename col_name col_data
100 clark JOB MANAGER
100 clark DEPTNO 40
100 clark SAL 1000
I am writing like this..
select empno, ename,
'JOB' as column_name,
job as column_data
from test_emp
union all
select empno, ename,
'DEPTNO' as column_name,
deptno as column_data
from test_emp
union all
select empno, ename,
'SAL' as column_name,
sal as column_data
from test_emp
Is there any other alternative?