Oracle行转列技巧和函数

编写行转列测试sql:

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;