英文:
Concatenate column values from multiple rows in Oracle SQL
问题
以下是你要的翻译：
我想创建一个查询，根据ID列合并多行的列数值。
这是我的表的结构：
ID Level
000 C
000 FK
111 F
222 FN
222 C
333 F
333 C
444 C
期望的结果应该如下：
ID Level
000 C - FK
111 F
222 FN - C
333 F - C
444 C
我该如何在Oracle SQL Developer中实现这个？
英文:
I want to create a query to concatenate column values from multiple rows based in an ID column.
This is the structure of my table:
ID Level
000 C
000 FK
111 F
222 FN
222 C
333 F
333 C
444 C
The expected result should be like this:
ID Level
000 C - FK
111 F
222 FN - C
333 F - C
444 C
How can I do this in Oracle SQL Developer?
答案1
得分: 1
I will translate the provided SQL code snippet:
Is `level` really your column name? Anyway, use `listagg`:
Sample data:
SQL> with test (id, c_level) as
2 (select '000', 'C' from dual union all
3 select '000', 'FK' from dual union all
4 select '111', 'F' from dual union all
5 select '222', 'FN' from dual union all
6 select '222', 'C' from dual union all
7 select '333', 'F' from dual union all
8 select '333', 'C' from dual union all
9 select '444', 'C' from dual
10 )
Query:
11 select id,
12 listagg(c_level, ' - ') within group (order by null) result
13 from test
14 group by id
15 order by id;
ID RESULT
--- ----------
000 C - FK
111 F
222 FN - C
333 F - C
444 C
SQL>;
Please let me know if you need further assistance.
英文:
Is level really your column name? Anyway, use listagg:
Sample data:
SQL> with test (id, c_level) as
2 (select '000', 'C' from dual union all
3 select '000', 'FK' from dual union all
4 select '111', 'F' from dual union all
5 select '222', 'FN' from dual union all
6 select '222', 'C' from dual union all
7 select '333', 'F' from dual union all
8 select '333', 'C' from dual union all
9 select '444', 'C' from dual
10 )
Query:
11 select id,
12 listagg(c_level, ' - ') within group (order by null) result
13 from test
14 group by id
15 order by id;
ID RESULT
--- ----------
000 C - FK
111 F
222 FN - C
333 F - C
444 C
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论