连接Oracle SQL中多行的列值

Question

我想创建一个查询来连接基于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中做到这一点?

Answer 1

level真的是你的列名吗?无论如何，使用listagg:

样本数据:

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    )

查询:

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>