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 fromtempunpivot(value for title in (idxkey1,idxkey2,idxkey3))t
执行结果
