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