[오라클] 정규식을 이용한 컬럼값 종으로 떨구기

SELECT PRDT_CD

      ,REGEXP_SUBSTR(PRICE, '[^|]+', 1, LV) AS VAL

FROM

(

  SELECT PRDT_CD

        ,PRICE

        ,REGEXP_COUNT(PRICE, '[|]') AS CNT

  FROM

  (

    SELECT '휴대폰' AS PRDT_CD

           ,'100|200' AS PRICE

    FROM DUAL

    UNION ALL

    SELECT '냉장고' AS PRDT_CD

          ,'100|200|300|400' AS PRICE

    FROM DUAL

  )  

) V1,

(

  SELECT LEVEL LV

  FROM DUAL

  CONNECT BY LEVEL <= 10

) V2

WHERE CNT + 1 >= LV

ORDER BY PRDT_CD

        ,VAL 

PRDT_CD VAL

-----------

냉장고 100

냉장고 200

냉장고300

냉장고 400

휴대폰 100

휴대폰 200

----- EOF -----