oracle 多行拼接sql示例

SELECT   n_mat_remark, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList

    FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_mat_remark ORDER BY n_mat_remark,

                  lvl DESC) rn,

                 n_mat_remark, text

            FROM (SELECT     n_mat_remark, LEVEL lvl,

                             SYS_CONNECT_BY_PATH (c_name,'/') text

                        FROM (SELECT   n_mat_remark, c_name as c_name,

                                       ROW_NUMBER () OVER (PARTITION BY n_mat_remark ORDER BY n_mat_remark,c_name) x

                                  FROM T_SC_MOVIES_AUTHORS

                              ORDER BY n_mat_remark, c_name) a

                  CONNECT BY n_mat_remark = PRIOR n_mat_remark AND x - 1 = PRIOR x))

   WHERE rn = 1 and n_mat_remark=1;

   /*替换：表名：T_SC_MOVIES_AUTHORS，聚合列：n_mat_remark，拼接列：c_name,分隔符为逗号

   */