10-96 グループ化して分析関数

SQLパズル

TestTable
Code  ID  Val
----  --  ---
   1   1  A
   1   1  x
   1   1  A
   1   1  x
   1   1  A
   1   1  A
   1   1  A
   1   2  B
   1   2  B
   1   2  B
   1   2  x
   1   2  B
   1   2  x
   1   3  A
   1   3  A
   1   3  K
   1   3  A
   1   3  A
   1   3  A
   1   4  C
   1   4  C
   1   4  x
   1   4  C
   1   4  C
   1   4  x
   1   4  C
   1   5  D
   1   5  B
   1   5  D
   1   5  D
   1   5  D
   1   6  E
   1   6  F
   1   6  E
   1   6  M
   1   6  E
   1   7  L
   1   7  F
   1   7  F
   1   8  G
   1   8  G
   1   8  G
   1   8  G
   1   8  G
   1   8  G
   2   1  R
   2   1  x
   2   1  R
   2   1  x
   2   1  R
   2   1  R
   2   1  R
   2   2  T
   2   2  Z
   2   2  T
   2   2  x
   2   2  T
   2   2  x
   2   3  R
   2   3  R
   2   3  K
   2   3  R
   2   3  R
   2   3  R
   2   4  C
   2   4  C
   2   4  x
   2   4  C
   2   4  C
   2   4  x
   2   4  C
   2   5  W
   2   5  T
   2   5  W
   2   5  W
   2   5  W
   2   6  P
   2   6  P
   2   6  P
   2   6  P
   2   6  P
   2   7  L
   2   7  F
   2   7  F
   2   8  G
   2   8  G
   2   8  G
   2   8  G
   2   8  G
   2   8  G

CodeとIDごとの、
Valの数を求め、
複数なら、Valには、'Var'と出力し、
Valと、'x'と、Valの数をカンマ区切りで文字列結合して、Varに出力する

出力結果
Code  ID  Val  Var
----  --  ---  -----------
   1   1  A    null
   1   2  B    null
   1   3  Var  5xA,1xK
   1   4  C    null
   1   5  Var  4xD,1xB
   1   6  Var  3xE,1xF,1xM
   1   7  Var  2xF,1xL
   1   8  G    null
   2   1  R    null
   2   2  Var  3xT,1xZ
   2   3  Var  5xR,1xK
   2   4  C    null
   2   5  Var  4xW,1xT
   2   6  P    null
   2   7  Var  2xF,1xL
   2   8  G    null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create Table TestTable as
select 1 as Code,1 as ID,'A' as Val from dual
union all select 1,1,'x' from dual
union all select 1,1,'A' from dual
union all select 1,1,'x' from dual
union all select 1,1,'A' from dual
union all select 1,1,'A' from dual
union all select 1,1,'A' from dual
union all select 1,2,'B' from dual
union all select 1,2,'B' from dual
union all select 1,2,'B' from dual
union all select 1,2,'x' from dual
union all select 1,2,'B' from dual
union all select 1,2,'x' from dual
union all select 1,3,'A' from dual
union all select 1,3,'A' from dual
union all select 1,3,'K' from dual
union all select 1,3,'A' from dual
union all select 1,3,'A' from dual
union all select 1,3,'A' from dual
union all select 1,4,'C' from dual
union all select 1,4,'C' from dual
union all select 1,4,'x' from dual
union all select 1,4,'C' from dual
union all select 1,4,'C' from dual
union all select 1,4,'x' from dual
union all select 1,4,'C' from dual
union all select 1,5,'D' from dual
union all select 1,5,'B' from dual
union all select 1,5,'D' from dual
union all select 1,5,'D' from dual
union all select 1,5,'D' from dual
union all select 1,6,'E' from dual
union all select 1,6,'F' from dual
union all select 1,6,'E' from dual
union all select 1,6,'M' from dual
union all select 1,6,'E' from dual
union all select 1,7,'L' from dual
union all select 1,7,'F' from dual
union all select 1,7,'F' from dual
union all select 1,8,'G' from dual
union all select 1,8,'G' from dual
union all select 1,8,'G' from dual
union all select 1,8,'G' from dual
union all select 1,8,'G' from dual
union all select 1,8,'G' from dual
union all select 2,1,'R' from dual
union all select 2,1,'x' from dual
union all select 2,1,'R' from dual
union all select 2,1,'x' from dual
union all select 2,1,'R' from dual
union all select 2,1,'R' from dual
union all select 2,1,'R' from dual
union all select 2,2,'T' from dual
union all select 2,2,'Z' from dual
union all select 2,2,'T' from dual
union all select 2,2,'x' from dual
union all select 2,2,'T' from dual
union all select 2,2,'x' from dual
union all select 2,3,'R' from dual
union all select 2,3,'R' from dual
union all select 2,3,'K' from dual
union all select 2,3,'R' from dual
union all select 2,3,'R' from dual
union all select 2,3,'R' from dual
union all select 2,4,'C' from dual
union all select 2,4,'C' from dual
union all select 2,4,'x' from dual
union all select 2,4,'C' from dual
union all select 2,4,'C' from dual
union all select 2,4,'x' from dual
union all select 2,4,'C' from dual
union all select 2,5,'W' from dual
union all select 2,5,'T' from dual
union all select 2,5,'W' from dual
union all select 2,5,'W' from dual
union all select 2,5,'W' from dual
union all select 2,6,'P' from dual
union all select 2,6,'P' from dual
union all select 2,6,'P' from dual
union all select 2,6,'P' from dual
union all select 2,6,'P' from dual
union all select 2,7,'L' from dual
union all select 2,7,'F' from dual
union all select 2,7,'F' from dual
union all select 2,8,'G' from dual
union all select 2,8,'G' from dual
union all select 2,8,'G' from dual
union all select 2,8,'G' from dual
union all select 2,8,'G' from dual
union all select 2,8,'G' from dual;

SQL

col Code for 9999
col ID   for 99
col Val  for a4
col Var  for a20

--■■■Connect_by_IsLeafを使う方法(10g以降)■■■
select Code,ID,
decode(Level,1,Val,'Var') as Val,
case when Level >= 2
     then SubStr(sys_connect_by_path(ValCount,','),2) end as Var
from (select Code,ID,Val,
      to_char(count(*)) || 'x' || Val as ValCount,
      Row_Number() over(partition by Code,ID order by count(*) desc,Val) as Rank
        from TestTable
       where Val != 'x'
      group by Code,ID,Val)
where Connect_by_IsLeaf = 1
Start With Rank = 1
connect by prior Code = Code
       and prior ID   = ID
       and prior Rank = Rank-1
order by Code,ID;

--■■■Connect_by_IsLeafを使わない方法■■■
select Code,ID,
decode(Level,1,Val,'Var') as Val,
case when Level >= 2
     then SubStr(sys_connect_by_path(ValCount,','),2) end as Var
from (select Code,ID,Val,
      to_char(count(*)) || 'x' || Val as ValCount,
      Row_Number() over(partition by Code,ID order by count(*) desc,Val) as Rank,
      count(*) over(partition by Code,ID) as MaxLevel
        from TestTable
       where Val != 'x'
      group by Code,ID,Val)
where Level = MaxLevel
Start With Rank = 1
connect by prior Code = Code
       and prior ID   = ID
       and prior Rank = Rank-1
order by Code,ID;

解説

インラインビューで、
グループ化して分析関数を使ってます

---