oracle 行转列，列转行

1.行转列

1.未行转列的sql

select idx_key,idx_value,schoolcode from tb_ZS_coreidx_data where idx_key in (
'IDX_01_00_ZQRZZXSS',
'IDX_01_01_BSZRJSS',
'IDX_01_01_XSZRJSS') and schoolcode='10268'

结果

经过行转列后

with temp as(
select idx_key,idx_value,schoolcode from tb_ZS_coreidx_data where idx_key in (
'IDX_01_00_ZQRZZXSS',
'IDX_01_01_BSZRJSS',
'IDX_01_01_XSZRJSS') and schoolcode='10268'
)
select * from （select schoolcode,idx_key,idx_value from temp）pivot (max(idx_value) for idx_key in ('IDX_01_00_ZQRZZXSS' as IDX_01_00_ZQRZZXSS,'IDX_01_01_BSZRJSS' as IDX_01_01_BSZRJSS,
'IDX_01_01_XSZRJSS' as IDX_01_01_XSZRJSS));

执行结果

2.列转行

1.未列转行sql

 select a.idx_key as idxkey1,b.idx_key as idxkey2,c.idx_key as idxkey3 from (
select IDX_KEY from tb_ZS_coreidx_data where schoolcode='10268' and idx_key in ('IDX_01_00_ZQRZZXSS' )) a ,
(select IDX_KEY from tb_ZS_coreidx_data where schoolcode='10268' and idx_key in ('IDX_01_01_BSZRJSS')) b ,
(select IDX_KEY from tb_ZS_coreidx_data where schoolcode='10268' and idx_key in ('IDX_01_01_XSZRJSS')) c

2.执行结果

经过列转行sql

with temp as(
 select a.idx_key as idxkey1,b.idx_key as idxkey2,c.idx_key as idxkey3 from (
select IDX_KEY from tb_ZS_coreidx_data where schoolcode='10268' and idx_key in ('IDX_01_00_ZQRZZXSS' )) a ,
(select IDX_KEY from tb_ZS_coreidx_data where schoolcode='10268' and idx_key in ('IDX_01_01_BSZRJSS')) b ,
(select IDX_KEY from tb_ZS_coreidx_data where schoolcode='10268' and idx_key in ('IDX_01_01_XSZRJSS')) c
)
select title,value from
temp
unpivot
(value for title in (idxkey1,idxkey2,idxkey3))t

执行结果