10-16 Group_Concat関数を模倣(最大数固定)

SQLパズル

テーブル
no  area
--  ----
 1   000
 2   001
 3   002
 3   003
 3   004
 4   006
 4   007

noに対応するareaをカンマ区切りで出力する。
(areaは、一つのnoにつき最大で3つまでとする)

出力結果
no  concat_area
--  -----------
1   000
2   001
3   002,003,004
4   006,007

データ作成スクリプト

create table テーブル as
select 1 as no,'000' as area from dual
union select 2,'001' from dual
union select 3,'002' from dual
union select 3,'003' from dual
union select 3,'004' from dual
union select 4,'006' from dual
union select 4,'007' from dual;

SQL

--■■■Lead関数を使う方法■■■
select no,RTrim(concat_area,',') as concat_area
from (select no,area,
      area || ',' ||
      Lead(area,1) over(partition by no order by area) || ',' ||
      Lead(area,2) over(partition by no order by area) as concat_area,
      min(area) over(partition by no) as Minarea
      from テーブル)
where area = Minarea;

--■■■Lag関数を使う方法■■■
select no,LTrim(concat_area,',') as concat_area
from (select no,area,
      Lag(area,2) over(partition by no order by area) || ',' ||
      Lag(area,1) over(partition by no order by area) || ',' ||
      area as concat_area,
      max(area) over(partition by no) as Maxarea
      from テーブル)
where area = Maxarea;

--■■■分析関数を使わない方法■■■
select no,
Rtrim(
max(decode(Rank,1,area)) || ',' ||
max(decode(Rank,2,area)) || ',' ||
max(decode(Rank,3,area)),',') as concat_area
from (select no,area,
      (select count(*)+1 from テーブル b
        where b.no = a.no
          and b.area < a.area) as Rank
        from テーブル a)
group by no
order by no;

解説

最大数が固定であれば、
Lag関数やLead関数を使って、
MySQLのGroup_Concat関数を模倣できます。

---

MySQL 4.1 リファレンスマニュアル :: 6.3.7.1 GROUP BY 関数

---