本文介绍了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

每行输出应如下所示:

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逗号分隔输出(按组)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 08:52
查看更多