oracle 单字段、多字段行转列

oracle 行转列（单字段）

--CHBGID,bw1为数据库原字段；beiyong, cdbh 为临时字段；in()里面的为需要转列的字段
select CHBGID,bw1 , beiyong, cdbh 
from BG_CHJL unpivot (cdbh for beiyong in (cdbh1,cdbh2,cdbh3,cdbh4,cdbh5,cdbh6,cdbh7,cdbh8))
WHERE CHBGID = '109624' ORDER BY BW1 ,TO_NUMBER(cdbh)

oracle 行转列（多字段）

--CHBGID,bw1为数据库原字段；cdbh,dchd 为临时字段；unpivot()里面的则为需要转列的临时字段名；in()里面的为需要转列的字段，多个用，分隔
select CHBGID,bw1,cdbh,dchd
from BG_CHJL 
unpivot((cdbh,dchd) for name in ((cdbh1,dchd1),
 								 (cdbh2,dchd2),
                                 (cdbh3,dchd3),
                                 (cdbh4,dchd4),
                                 (cdbh5,dchd5),
                                 (cdbh6,dchd6),
                                 (cdbh7,dchd7),
                                 (cdbh8,dchd8)
                                ) 
        )  WHERE CHBGID = '109624' ORDER BY BW1 ,TO_NUMBER(cdbh)