ORACLE 行转列及函数定义

时间：2010-12-10 来源：钻石眼泪

SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ou_code, ';')), ';') AS RESULT
      FROM (SELECT category_id, ou_code, RN, LEAD(RN) OVER(ORDER BY RN) RN1
              FROM (SELECT category_id,
                           ou_code,
                           ROW_NUMBER() OVER(ORDER BY category_id, ou_code DESC) RN
                      FROM (SELECT t.category_id, t.ou_code
                              FROM bpm_dcs_category_ou t
                             WHERE t.category_id=1000085)))
     START WITH RN1 IS NULL
            AND category_id = 1000085
    CONNECT BY RN1 = PRIOR RN;

函数定义

CREATE OR REPLACE FUNCTION GET_OU_LIST(I_CATRGORY_ID IN NUMBER) RETURN VARCHAR2 IS
    OU_LIST VARCHAR2(4000) := '';
BEGIN
    SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(OU_CODE, ';')), ';') INTO OU_LIST
      FROM (SELECT CATEGORY_ID, OU_CODE, RN, LEAD(RN) OVER(ORDER BY RN) RN1
              FROM (SELECT CATEGORY_ID,
                           OU_CODE,
                           ROW_NUMBER() OVER(ORDER BY CATEGORY_ID, OU_CODE DESC) RN
                      FROM (SELECT T.CATEGORY_ID, T.OU_CODE
                              FROM BPM_DCS_CATEGORY_OU T
                             WHERE T.CATEGORY_ID=I_CATRGORY_ID)))
     START WITH RN1 IS NULL
            AND CATEGORY_ID = I_CATRGORY_ID
    CONNECT BY RN1 = PRIOR RN;
    RETURN OU_LIST;
END GET_OU_LIST;