トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

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