10-95 IDごとの文字の数を取得

SQLパズル

StringTable
ID  Val
--  ---
 1  A
 1  x
 2  B
 2  x
 2  B
 2  x
 3  A
 3  K
 4  C
 4  C
 4  x
 4  x
 5  D
 5  B
 6  E
 7  L
 7  F
 7  F
 8  G
 8  G

x以外の文字列が存在するIDごとの、
Valの重複の除いた数を求め、
2以上なら'a string'、
そうでない場合は、Valを出力する

出力結果
ID  Val
--  --------
 1  A
 2  B
 3  a string
 4  C
 5  a string
 6  E
 7  a string
 8  G

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

データ作成スクリプト

create table StringTable as
select 1 as ID,'A' as Val from dual
union all select 1,'x' from dual
union all select 2,'B' from dual
union all select 2,'x' from dual
union all select 2,'B' from dual
union all select 2,'x' from dual
union all select 3,'A' from dual
union all select 3,'K' from dual
union all select 4,'C' from dual
union all select 4,'C' from dual
union all select 4,'x' from dual
union all select 4,'x' from dual
union all select 5,'D' from dual
union all select 5,'B' from dual
union all select 6,'E' from dual
union all select 7,'L' from dual
union all select 7,'F' from dual
union all select 7,'F' from dual
union all select 8,'G' from dual
union all select 8,'G' from dual;

SQL

select distinct ID,
case when count(distinct Val) over(partition by ID) > 1
then 'a string' else Val end as Val
from StringTable
where val != 'x'
order by ID;

解説

case式とcount関数を組み合わせてます

---