DB2逗号分隔输出（按组）

Question

是否有内置的函数用于逗号分隔的列值DB2 SQL？

示例：如果存在带有的列，ID并且它具有3行相同的列，ID但具有三个不同的角色，则数据应以逗号连接。

ID   | Role

------------

4555 | 2

4555 | 3

4555 | 4

每行输出应类似于以下内容：

4555 2,3,4

Answer 1

LISTAGG函数是DB2 LUW 9.7中的新函数参见示例：create table myTable (id int, category int);insert into myTable values (1, 1);insert into myTable values (2, 2);insert into myTable values (5, 1);insert into myTable values (3, 1);insert into myTable values (4, 2);示例：在分组列中不按任何顺序进行选择select category, LISTAGG(id, ', ') as ids from myTable group by category;结果：CATEGORY  IDS--------- -----1         1, 5, 32         2, 4示例：在分组列中使用order by子句进行选择select  category,  LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as idsfrom myTablegroup by category;结果：CATEGORY  IDS--------- -----1         1, 3, 52         2, 4

Answer 2

我认为通过这个较小的查询，您可以做您想做的事情。这等效于DB2中MySQL的GROUP_CONCAT。SELECT NUM, SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',ROLES))) as VARCHAR(1024)), 3) as ROLESFROM mytable GROUP BY NUM;这将输出类似：NUM   ROLES----  -------------1     111, 333, 5552     222, 444假设您的原始结果是这样的：NUM   ROLES----  ---------1     1112     2221     3332     4441     555

Answer 3

根据您拥有的DB2版本，可以使用XML函数来实现此目的。带有一些数据的示例表create table myTable (id int, category int);insert into myTable values (1, 1);insert into myTable values (2, 2);insert into myTable values (3, 1);insert into myTable values (4, 2);insert into myTable values (5, 1);使用xml函数汇总结果select category,&nbsp;&nbsp; &nbsp; xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id) ) as varchar(1000)) as ids&nbsp;&nbsp; &nbsp; from myTable&nbsp; &nbsp; group by category;结果：CATEGORY IDS&nbsp;-------- ------------------------&nbsp; &nbsp; &nbsp; &nbsp; 1 <x>1</x><x>3</x><x>5</x>&nbsp; &nbsp; &nbsp; &nbsp; 2 <x>2</x><x>4</x>使用替换使结果看起来更好select category,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; replace(&nbsp; &nbsp; &nbsp; &nbsp; replace(&nbsp; &nbsp; &nbsp; &nbsp; replace(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id) ) as varchar(1000))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , '</x><x>', ',')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , '<x>', '')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , '</x>', '') as ids&nbsp;&nbsp; &nbsp; from myTable&nbsp; &nbsp; group by category;清理结果CATEGORY IDS&nbsp;-------- -----&nbsp; &nbsp; &nbsp; &nbsp; 1 1,3,5&nbsp; &nbsp; &nbsp; &nbsp; 2 2,4刚看到使用XMLELEMENT的XMLTEXT而不是一个更好的解决方案在这里。