Oracle的sys_connect_by_path函数

sys_connect_by_path函数

用于多列转行

语法格式
select … sys_connect_by_path(column_name,’connect_symbol’) from table
start with … connect by … prior

语法说明
column_name，字段名
connect_symbol，两个字段之间的连接符号

start with，开始的地方，遍历的起点
connect by，来寻找下一条记录，直到迭代找不到相应记录为止
指定递归(连接)条件，如果条件不满足则递归结束
prior，参考值，写在谁的前面谁就作为父级值（参照值）

SELECT
    c1 AS ASSAY_EQUIPMENT_ID,
    LTRIM (
        SYS_CONNECT_BY_PATH (c2, ','),
        ','
    ) ASSAY_ITEM_IDS,
    LTRIM (
        SYS_CONNECT_BY_PATH (c3, ','),
        ','
    ) ASSAY_ITEM_NAMES
FROM
    (
        SELECT
            c1,
            c2,
            c3,
            ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) rn
        FROM
            (
                SELECT
                    R1.ASSAY_EQUIPMENT_ID c1,
                    R1.ASSAY_ITEM_ID c2,
                    T1.ASSAY_ITEM_NAME c3
                FROM
                    CNF_ASSAY_ITEM_EQUI_RELA R1,
                    DIC_ASSAY_ITEM T1
                WHERE
                    R1.ASSAY_ITEM_ID = T1. ID
            )
    )
WHERE
    CONNECT_BY_ISLEAF = 1 CONNECT BY PRIOR rn + 1 = rn
AND PRIOR c1 = c1 START WITH rn = 1