按组的 DB2 逗号分隔输出

问题描述

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

Is there a built in function for comma separated column values in DB2 SQL?

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

Example: If there are columns with an ID and it has 3 rows with the same ID but have three different roles, the data should be concatenated with a comma.

ID   | Role
------------
4555 | 2
4555 | 3
4555 | 4

每行的输出应如下所示:

The output should look like the following, per row:

4555 2,3,4

推荐答案

LISTAGG 函数是 DB2 LUW 9.7 中的新函数

LISTAGG function is new function in 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);

示例:在分组列中选择无任何顺序

example: select without any order in grouped column

select category, LISTAGG(id, ', ') as ids from myTable group by category;

结果:

CATEGORY  IDS
--------- -----
1         1, 5, 3
2         2, 4

示例:在分组列中使用 order by 子句进行选择

example: select with order by clause in grouped column

select
  category,
  LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids
from myTable
group by category;

结果:

CATEGORY  IDS
--------- -----
1         1, 3, 5
2         2, 4

这篇关于按组的 DB2 逗号分隔输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！