How to get a similar value in Oracle

2020-05-01 10:12发布

I have a table of two columns

Col1  Col2
A        1
A        2
A        3
B        1
B        2
B        3

Output I need is like this

Col1    Col2
A       1
A       1,2
A       1,2,3
B       1
B       1,2
B       1,2,3

Thank you in advance.

标签: mysql oracle
2条回答
够拽才男人
2楼-- · 2020-05-01 10:21

Here is a solution which would work for MySQL. It uses a correlated subquery in the select clause to group concatenate together Col2 values. The logic is that we only aggregate values which are less than or equal to the current row, for a given group of records sharing the same Col1 value.

SELECT
    Col1,
    (SELECT GROUP_CONCAT(t2.Col2 ORDER BY t2.Col2) FROM yourTable t2
     WHERE t2.Col2 <= t1.Col2 AND t1.Col1 = t2.Col1) Col2
FROM yourTable t1
ORDER BY
    t1.Col1,
    t1.Col2;

enter image description here

Demo

Here is the same query in Oracle:

SELECT
    Col1,
    (SELECT LISTAGG(t2.Col2, ',') WITHIN GROUP (ORDER BY t2.Col2) FROM yourTable t2
     WHERE t2.Col2 <= t1.Col2 AND t1.Col1 = t2.Col1) Col2
FROM yourTable t1
ORDER BY
    t1.Col1,
    t1.Col2;

Demo

Note that the only real change is substituting LISTAGG for GROUP_CONCAT.

查看更多
我欲成王,谁敢阻挡
3楼-- · 2020-05-01 10:33
with s (Col1, Col2) as (
select 'A', 1 from dual union all
select 'A', 2 from dual union all
select 'A', 3 from dual union all
select 'B', 1 from dual union all
select 'B', 2 from dual union all
select 'B', 3 from dual)
select col1, ltrim(sys_connect_by_path(col2, ','), ',') path
from s
start with col2 = 1
connect by prior col2 = col2 - 1 and prior col1 = col1;

C PATH
- ----------
A 1
A 1,2
A 1,2,3
B 1
B 1,2
B 1,2,3

6 rows selected.
查看更多
登录 后发表回答