10-218 wmsys.wm_concat

SQLパズル

TebleA
ID  金額  区分
--  ----  ----
 1   100     0
 2   200     1
 3   300     0
 4   400     0
 5   500     1
 6   600     0
 7   700     0
 8   800     1
 9   900     0

区分が0のレコードは、そのまま出力
区分が1のレコードは、まとめて出力。(金額は合計、IDは全IDをカンマ区切りでつなげる)

出力結果
ID     金額  区分
-----  ----  ----
1       100     0
3       300     0
4       400     0
6       600     0
7       700     0
9       900     0
2,5,8  1500     1

データ作成スクリプト

create table TebleA(ID,金額,区分) as
select 1,100,0 from dual union
select 2,200,1 from dual union
select 3,300,0 from dual union
select 4,400,0 from dual union
select 5,500,1 from dual union
select 6,600,0 from dual union
select 7,700,0 from dual union
select 8,800,1 from dual union
select 9,900,0 from dual;

SQL

col ID for a10

--■■■XMLAggとXMLElementを使う方法■■■
select substr(replace(replace(XMLAgg(XMLElement("dummy",ID) order by ID),
                      '</dummy>'),'<dummy>',','),2) as ID,
sum(金額) as 金額,区分
  from TebleA
group by decode(区分,0,ID),区分
order by decode(区分,0,ID);

--■■■wmsys.wm_concatを使う方法■■■
select wmsys.wm_concat(ID) as ID,
sum(金額) as 金額,区分
  from TebleA
group by decode(区分,0,ID),区分
order by decode(区分,0,ID);

--■■■ListAgg関数を使う方法(11gR2以降)■■■
select ListAgg(ID,',') WITHIN GROUP (ORDER BY ID) as ID,
sum(金額) as 金額,区分
  from TebleA
group by decode(区分,0,ID),区分
order by decode(区分,0,ID);

解説

wmsys.wm_concatは、Oracle11gのマニュアルにすらのってないので、
注意して使う必要があります。

Oracle11gR2からは、ListAggが使えます。
マニュアル --- ListAgg

10-17 Group_Concat関数を階層問い合わせで模倣
10-18 Group_Concat関数をXMLの関数で模倣
10-56 結合して階層問い合わせ
10-262 累計を求めるwm_concatをXML関数で模倣
model句04 wmsys.wm_concatの代用
model句22 MySQLのgroup_concatもどき



wmsys.wm_concatの実験結果

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

SQL> create table IDTable(ID,Val) as
  2  select 10,'abc' from dual union all
  3  select 10,'abc' from dual union all
  4  select 10,'def' from dual union all
  5  select 10,'def' from dual union all
  6  select 20,'ghi' from dual union all
  7  select 20,'jkl' from dual union all
  8  select 20,'mno' from dual union all
  9  select 20,'mno' from dual;

SQL> col ConcatVal for a40

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL> select ID,wmsys.wm_concat(Val) as ConcatVal
  2    from IDTable
  3  group by ID
  4  order by ID;

ID  ConcatVal
--  ----------------
10  abc,abc,def,def
20  ghi,jkl,mno,mno

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL> select ID,wmsys.wm_concat(distinct Val) as ConcatVal
  2    from IDTable
  3  group by ID
  4  order by ID;

ID  ConcatVal
--  -------------
10  abc,def
20  ghi,jkl,mno

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
MySQLのgroup_Concatと同じ構文だとエラーになりました。
SQL> select ID,wmsys.wm_concat(Val order by Val) as ConcatVal
  2    from IDTable
  3  group by ID
  4  order by ID;
select ID,wmsys.wm_concat(Val order by Val) as ConcatVal
                                          *
ORA-00907: 右カッコがありません。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL> select ID,Val,wmsys.wm_concat(Val) over(partition by ID) as ConcatVal
  2    from IDTable
  3  order by ID;

ID  VAL  ConcatVal
--  ---  -----------------
10  abc  abc,abc,def,def
10  abc  abc,abc,def,def
10  def  abc,abc,def,def
10  def  abc,abc,def,def
20  ghi  ghi,jkl,mno,mno
20  jkl  ghi,jkl,mno,mno
20  mno  ghi,jkl,mno,mno
20  mno  ghi,jkl,mno,mno

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL> select ID,Val,wmsys.wm_concat(Val) over(order by ID,Val) as ConcatVal
  2    from IDTable
  3  order by ID;

ID  VAL  ConcatVal
--  ---  ----------------------------------
10  abc  abc,abc
10  abc  abc,abc
10  def  abc,abc,def,def
10  def  abc,abc,def,def
20  ghi  abc,abc,def,def,ghi
20  jkl  abc,abc,def,def,ghi,jkl
20  mno  abc,abc,def,def,ghi,jkl,mno,mno
20  mno  abc,abc,def,def,ghi,jkl,mno,mno

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL> select max(ID),
  2  wmsys.wm_concat(Val) Keep(Dense_Rank Last order by ID) as ConcatVal
  3    from IDTable;

  MAX(ID)  ConcatVal
---------  ---------------
       20  ghi,jkl,mno,mno

---