Aug 25, 2018

XMLAGG in Teradata

SQLServerやPostgreSQLにあるstring_aggやMySQLのgroup_concatですが残念なことにTeradataでは存在しません。代わりにという訳ではないですが、xmlaggという集約関数があり、これを使うと同じようなことが実現できます。

まずはデータを準備します。
drop table test_xml_agg
;
create table test_xml_agg (
 c1 int
,c2 int
,c3 varchar(10)
)
;
insert into test_xml_agg values (1,1,'hello');
insert into test_xml_agg values (1,2,'world');
insert into test_xml_agg values (2,1,'this');
insert into test_xml_agg values (2,2,'is');
insert into test_xml_agg values (2,3,'xmlagg');

上記のデータを1列目で集約し、2列目の順番で一度3列目を横に展開しカンマで結合するというクエリを書いてみます。
select
 c1  
 ,trim(trailing ',' from xmlagg(c3 || ',' order by c2) (varchar(100))) as string_agg
from test_xml_agg
group by 1
;

Result Set
c1 string_agg
1  hello, world
2  this, is, xmlagg


string_aggに比べるとごちゃちゃしていますが、まずxmlagg(c3 || ',' order by c2)で2列目の昇順で3列目を結合していくことを表しています。続いて(varchar(100))で型をsysudtlib.xmlからvarcharにキャストし、最後に末尾のカンマを除いています。

2018/10/19追記
tdstats.udfconcatという関数がひそかに存在すると同僚が教えてくれました。AutoStatsで統計対象カラム名を連結するための関数とのことです。
UDFCONCAT