listagg v9.7.4新增的聚集函数,行转列

转帖：http://www.db2china.net//home/space.php?uid=35802&do=blog&id=20857

我们曾为拼接不同行中的字段烦恼，学习一下v9.7.4新增的listagg聚集函数 ：

A new aggregate function, LISTAGG, has been added. The LISTAGG function aggregates a set of string elements into one string by concatenating the strings. Optionally, a separator string can be provided which is inserted between contiguous input strings. For more information, see  LISTAGG aggregate function .
示例：
db2 => create table tt(grp int, name char(5));
DB20000I  The SQL command completed successfully.
db2 => insert into tt values (1, 'Jim'), (1,'Tom'), (1,'Jane'),(2,'Tony'),(2,'Mike');
DB20000I  The SQL command completed successfully.
db2 => select * from tt;

GRP         NAME
----------- -----
          1 Jim
          1 Tom
          1 Jane
          2 Tony
          2 Mike

  5 record(s) selected.
db2 => select grp, listagg(name, ',') WITHIN GROUP(ORDER BY name) as names from tt group by grp;

GRP         NAMES                                                                                   
----------  ---------------------
          1 Jane ,Jim  ,Tom
          2 Mike ,Tony

2 record(s) selected.