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