ORACLE列转行示例

SELECT A.CODE_O "codeO",A.CODE_D "codeD",A.YEARS years,B.PATH,B.YEAR FROM (
SELECT CODE_O,CODE_D,(SELECT MAX(SUBSTR(SYS_CONNECT_BY_PATH(YEAR ,','),2))
FROM (SELECT YEAR,CODE_O,CODE_D,ROWNUM RN FROM (SELECT DISTINCT CODE_O,CODE_D,YEAR FROM HW_ADJOIN_NODE ORDER BY YEAR)
WHERE CODE_O =A.CODE_O AND CODE_D=A.CODE_D)
START WITH RN=1 CONNECT BY RN=ROWNUM) YEARS FROM (SELECT DISTINCT CODE_O,CODE_D FROM HW_ADJOIN_NODE) A) A
LEFT JOIN HW_ADJOIN_NODE B ON A.CODE_O=B.CODE_O AND A.CODE_D=B.CODE_D AND substr(A.YEARS,-4)=B.YEAR;

其中YEAR 为要列转行的字段，主要应用MAX(SUBSTR(SYS_CONNECT_BY_PATH(YEAR ,','),2))