SQL のケーススタディ: 文字列のマージと分割

--方法一
SELECT d.dept_name,wm_concat(e.emp_name) FROM employee e
INNER JOIN department d ON d.dept_id=e.dept_id
GROUP BY d.dept_name;

--方法二
SELECT d.dept_name,
LISTAGG (e.emp_name, ',') WITHIN GROUP (ORDER BY e.emp_name) names
FROM employee e
INNER JOIN department d ON d.dept_id=e.dept_id
GROUP BY d.dept_name;

--方法一
WITH  t (id, name, sub, str) AS (
    SELECT id, name, substr(class, 1, instr(class, '、')-1), substr(concat(class,'、'), instr(class, '、')+1) 
    FROM movies
    
    UNION ALL
    
    SELECT id, name,substr(str, 1, instr(str, '、')-1), substr(str, instr(str, '、')+1)
    FROM t WHERE instr(str, '、')>0
) 
 
SELECT id, name, sub
FROM t
ORDER BY id;

--方法二
SELECT m.name,t.column_value FROM movies m,TABLE(SPLIT(m.class,'、')) t;

create or replace function split (p_list clob, p_sep varchar2 := ',')
  return tabletype
  pipelined
 
is
  l_idx    pls_integer;
  v_list  varchar2 (32676) := to_char(p_list);
begin
  loop
      l_idx  := instr (v_list, p_sep);
 
      if l_idx > 0
      then
        pipe row (substr (v_list, 1, l_idx - 1));
        v_list  := substr (v_list, l_idx + length (p_sep));
      else
        pipe row (v_list);
        exit;
      end if;
  end loop;
end;

create or replace type tabletype as table of varchar2(32676);