英文:
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:
SELECT
ID,
LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name )
From my_table
GROUP BY ID;
What I have tried to replace ',' with '|' and remove the duplicates and trim the last delimiter is:
SELECT
ID,
RTRIM(REGEXP_REPLACE(LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name ), '([^,]+)(,|)+', '|'),'|')
From my_table
GROUP 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 应用于已经不重复的数据集：
WITH
temp
AS
(SELECT DISTINCT id, user_name
FROM my_table)
SELECT id,
LISTAGG (user_name, '|') WITHIN GROUP (ORDER BY user_name)
FROM temp
GROUP BY id;
英文:
First select distinct values, and then apply listagg to already distinct data set:
WITH
temp
AS
(SELECT DISTINCT id, user_name
FROM my_table)
SELECT id,
LISTAGG (user_name, '|') WITHIN GROUP (ORDER BY user_name)
FROM temp
GROUP BY id;
答案2
得分: 0
您可以使用 `collect` 聚合函数来获取不重复的值，然后在集合上使用 `listagg` 函数。
```sql
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 |
| 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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论