英文:
Concatenate column values from multiple rows in Oracle SQL
问题
以下是你要的翻译：
我想创建一个查询，根据ID列合并多行的列数值。
这是我的表的结构：
ID Level000 C000 FK111 F222 FN222 C333 F333 C444 C
期望的结果应该如下：
ID Level000 C - FK111 F222 FN - C333 F - C444 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 Level000 C000 FK111 F222 FN222 C333 F333 C444 C
The expected result should be like this:
ID Level000 C - FK111 F222 FN - C333 F - C444 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) as2 (select '000', 'C' from dual union all3 select '000', 'FK' from dual union all4 select '111', 'F' from dual union all5 select '222', 'FN' from dual union all6 select '222', 'C' from dual union all7 select '333', 'F' from dual union all8 select '333', 'C' from dual union all9 select '444', 'C' from dual10 )Query:11 select id,12 listagg(c_level, ' - ') within group (order by null) result13 from test14 group by id15 order by id;ID RESULT--- ----------000 C - FK111 F222 FN - C333 F - C444 CSQL>;
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) as2 (select '000', 'C' from dual union all3 select '000', 'FK' from dual union all4 select '111', 'F' from dual union all5 select '222', 'FN' from dual union all6 select '222', 'C' from dual union all7 select '333', 'F' from dual union all8 select '333', 'C' from dual union all9 select '444', 'C' from dual10 )
Query:
11 select id,12 listagg(c_level, ' - ') within group (order by null) result13 from test14 group by id15 order by id;ID RESULT--- ----------000 C - FK111 F222 FN - C333 F - C444 CSQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论