pivot xml

오라클 11g부터 제공하는 pivot 기능.

그런데 IN 조건에 select 문이 적용이 안되어 pivot 대신 pivot xml 사용. 쿼리 후 xml parsing 작업 필요.

SELECT
    FM_UID,
    NAME,
    EXTRACT(ELEMENT_NAME_XML, '/PivotSet').getStringVal() AS ITEMS
FROM (
    SELECT
        RR.FM_UID,
        FM.NAME,
        RR.ELEMENT_NAME,
        RR.VALUE
    FROM CAS_REQUEST RQ INNER JOIN CAS_REPORT RP
        ON RQ.OUID = 'RQ101000000001' AND RQ.OUID = RP.RQ_UID AND RP.USE_FLAG = '1'
        INNER JOIN CAS_REPORT_RESULT RR ON RP.OUID = RR.RP_UID
        INNER JOIN APS_FORM FM ON RR.FM_UID = FM.OUID
    ORDER BY RR.FM_UID
)
PIVOT XML (
    MAX(VALUE) AS VALUE FOR (ELEMENT_NAME) IN (ANY)
)
ORDER BY FM_UID;

----------------------------------------------------------------------------------------------------------------------

IRecordSet rs = dbSelect("dmResultView", paramMap, onlineCtx);
rs = PivotXMLUtil.instance().parsePivotXML(rs, "ELEMENT_NAME", "VALUE", "ITEMS");