oracle 列转为行

小易3506

　　1.可以用union all但繁琐，不灵活
　　2.采用decode+系统视图USER_TAB_COLS（推荐）:

　　

select
B.column_name,
decode(B.column_name,
'JOB_ID',
A.Job_Id,
'ATTR_1',
A.Atrr_1,
'PROC3',
A.Atrr_3,
null) value
from sm_job_control A,
(select column_name
from user_tab_cols
where column_id > 1
and table_name = 'SM_JOB_CONTROL') B
select C.column_name as key, C.value
from (select B.column_name,
decode(B.column_name,
'JOB_ID',
A.Job_Id,
'ATTR_1',
A.Atrr_1,
'ATTR_2',
A.Atrr_2,
'ATTR_3',
A.Atrr_3,
'ATTR_4',
A.Atrr_4,
'ATTR_5',
A.Atrr_5,
'F_ATTR_1',
A.f_Atrr_1,
'F_ATTR_2',
A.f_Atrr_2,
'F_ATTR_3',
A.f_Atrr_3,
'F_ATTR_4',
A.f_Atrr_4,
'F_ATTR_5',
A.f_Atrr_5,
null) value
from sm_job_control A,
(select s.COLUMN_NAME
from user_tab_cols s
where s.COLUMN_ID IN (2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
and table_name = 'SM_JOB_CONTROL') B
WHERE A.ID = 1) C
where C.value is not null