wm_concat与listagg

连接字符串
1.wm_concat
2.listagg

案例:
/*拆分regexp_substr 合并wm_concat 替换regexp_replace*/
WITH a AS
 (SELECT '1,a,1,b,2,a,2,c,3,d,3,e,4,y' a FROM dual)
SELECT b, wm_concat(c) d
  FROM (SELECT regexp_substr(a, '[^,]+', 2 * LEVEL - 1, LEVEL) b,
               regexp_substr(a, '[^,]+', 2 * LEVEL, LEVEL) c
          FROM a
        CONNECT BY LEVEL <
                   (length(a) - length(regexp_replace(a, '[^,]+', '')) + 1) / 2)
 GROUP BY b;

/*拆分regexp_substr 合并listagg 替换regexp_replace*/
WITH a AS
 (SELECT '1,a,1,b,2,a,2,c,3,d,3,e,4,y' a FROM dual)
SELECT b, listagg(c,',') within GROUP(ORDER BY b) d
  FROM (SELECT regexp_substr(a, '[^,]+', 2 * LEVEL - 1, LEVEL) b,
               regexp_substr(a, '[^,]+', 2 * LEVEL, LEVEL) c
          FROM a
        CONNECT BY LEVEL <
                   (length(a) - length(regexp_replace(a, '[^,]+', '')) + 1) / 2)
 GROUP BY b;
 
/*wm_concat*/
WITH a AS
 (SELECT 1 id, 'a' NAME
    FROM dual
  UNION ALL
  SELECT 1 id, 'b' NAME
    FROM dual
  UNION ALL
  SELECT 1 id, 'c' NAME
    FROM dual
  UNION ALL
  SELECT 2 id, 'b' NAME
    FROM dual
  UNION ALL
  SELECT 2 id, 'a' NAME
    FROM dual
  UNION ALL
  SELECT 3 id, 'c' NAME
    FROM dual
  UNION ALL
  SELECT 3 id, 'd' NAME
    FROM dual
  UNION ALL
  SELECT 4 id, 'b' NAME
    FROM dual)
SELECT id, wm_concat(NAME) NAME FROM a GROUP BY id;

/*不用group by 使用over(PARTITION BY XXX)*/
WITH temp AS
 (SELECT 500 population, 'China' nation, 'Guangzhou' city
    FROM dual
  UNION ALL
  SELECT 1500 population, 'China' nation, 'Shanghai' city
    FROM dual
  UNION ALL
  SELECT 500 population, 'China' nation, 'Beijing' city
    FROM dual
  UNION ALL
  SELECT 1000 population, 'USA' nation, 'New York' city
    FROM dual
  UNION ALL
  SELECT 500 population, 'USA' nation, 'Bostom' city
    FROM dual
  UNION ALL
  SELECT 500 population, 'Japan' nation, 'Tokyo' city
    FROM dual)
SELECT population,
       nation,
       city,
       listagg(city, ',') within GROUP(ORDER BY city) over(PARTITION BY nation) rank
  FROM temp