!!!DB2 再帰で区切りリストを作成
[DB2][Database]{{category SQL}}[DB2 再帰]

{{amazon 4873113156}}

*WITH句を使った再帰的なクエリで実現
*UNION ALLの上側は、各従業員に対する情報を返す
*UNION ALLの下側で再帰が発生
*WITH句内のWHERE 句で再帰条件を指定
*x.len+1 は評価された従業員数、部署の人数と比較し必要以上の再帰を抑制
 
 with x (workdept, cnt, list, empno, len) as (
     select workdept, count(*) over(partition by workdept), cast(lastname as varchar(200)),empno,1
     from employee
     union all
     select x.workdept,x.cnt, x.list || ' , ' || e.lastname, e.empno, x.len+1
     from employee e, x
     where e.workdept = x.workdept
     and e.empno > x.empno
 )
 select workdept, list 
 from x
 where len = cnt
 order by workdept

::結果
 WORKDEPT   LIST
 --------   -------------------------------------------------------------------------------------------------
 A00       HAAS , LUCCHESSI , O'CONNELL , HEMMINGER , ORLANDO
 B01       THOMPSON
 C01       KWAN , QUINTANA , NICHOLLS , NATZ
 D11       STERN , ADAMSON , PIANKA , YOSHIMURA , SCOUTTEN , WALKER , BROWN , JONES , LUTZ , YAMAMOTO , JOHN
 D21       PULASKI , JEFFERSON , MARINO , SMITH , JOHNSON , PEREZ , MONTEVERDE
 E01       GEYER
 E11       HENDERSON , SCHNEIDER , PARKER , SMITH , SETRIGHT , SCHWARTZ , SPRINGER
 E21       SPENSER , MEHTA , LEE , GOUNOT , WONG , ALONZO