英文:
How to change comma to pipe and remove duplicates and trim last delimiter in LISTAGG
问题
|ID|USER_NAME ||--|------------||1 |A1|B12|C32 ||2 |A1 ||3 |B12|C32 |
英文:
I have a query which result is: (EDIT: 13.06.2023 12:30)
|ID|USER_NAME ||--|--------------------||1 |A1,A1,A1,B12,B12,C32||2 |A1 ||3 |B12,C32 |
Query for the above result:
SELECTID,LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name )From my_tableGROUP BY ID;
What I have tried to replace ',' with '|' and remove the duplicates and trim the last delimiter is:
SELECTID,RTRIM(REGEXP_REPLACE(LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name ), '([^,]+)(,|)+', '|'),'|')From my_tableGROUP BY ID;
The output is:
|ID|USER_NAME ||--|------------||1 |A1|2|B12|C32||2 |A1 ||3 |B12|C32 |
EDIT:
The LISTAGG and REGEXP should be part of a longer query and not be a standalone SELECT (output of LISTAGG should be a column of a long query). The example should only show what output I want to have in the end.<br />
The second value for user_name and ID 1 is not correct.<br />
How can I get the correct values? What is wrong in the query?<br />
Thank you.
答案1
得分: 2
首先选择不重复的数值，然后将 listagg 应用于已经不重复的数据集：
WITHtempAS(SELECT DISTINCT id, user_nameFROM my_table)SELECT id,LISTAGG (user_name, '|') WITHIN GROUP (ORDER BY user_name)FROM tempGROUP BY id;
英文:
First select distinct values, and then apply listagg to already distinct data set:
WITHtempAS(SELECT DISTINCT id, user_nameFROM my_table)SELECT id,LISTAGG (user_name, '|') WITHIN GROUP (ORDER BY user_name)FROM tempGROUP BY id;
答案2
得分: 0
您可以使用 `collect` 聚合函数来获取不重复的值，然后在集合上使用 `listagg` 函数。```sqlcreate table sample_table (id int, user_name varchar2(10))
begininsert into sample_table values(1, 'A1');insert into sample_table values(1, 'A1');insert into sample_table values(1, 'A1');insert into sample_table values(1, 'B12');insert into sample_table values(1, 'B12');insert into sample_table values(1, 'C32');insert into sample_table values(2, 'A1');insert into sample_table values(3, 'B12');insert into sample_table values(3, 'C32');insert into sample_table values(3, 'B12');commit;end;
with grouped as (selectid,cast(collect(distinct user_name) as sys.odcivarchar2list) as qwefrom sample_tablegroup by id)selectid,(selectlistagg(column_value, '|') within group(order by column_value) as user_namefrom table(grouped.qwe)) as user_namefrom grouped
| ID | USER_NAME |
|---|---|
| 1 | A1 |
| 2 | A1 |
| 3 | B12 |
<details><summary>英文:</summary>You may use `collect` aggregation to get distinct values and then use `listagg` over collection.
create table sample_table (id int, user_name varchar2(10))
begin
insert into sample_table values(1, 'A1');
insert into sample_table values(1, 'A1');
insert into sample_table values(1, 'A1');
insert into sample_table values(1, 'B12');
insert into sample_table values(1, 'B12');
insert into sample_table values(1, 'C32');
insert into sample_table values(2, 'A1');
insert into sample_table values(3, 'B12');
insert into sample_table values(3, 'C32');
insert into sample_table values(3, 'B12');
commit;
end;/
with grouped as (
select
id,
cast(collect(distinct user_name) as sys.odcivarchar2list) as qwe
from sample_table
group by id
)
select
id,
(
select
listagg(column_value, '|') within group(order by column_value) as user_name
from table(grouped.qwe)
) as user_name
from grouped
| ID | USER\_NAME || --:|:---------|| 1 | A1\|B12\|C32 || 2 | A1 || 3 | B12\|C32 |[fiddle](https://dbfiddle.uk/b-BRd_QD)</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论