CODEWITHSUNDEEP
sqloracle-database
Haseeb
Haseeb

Reputation: 3

Cumulative string concatenation

I have a requirement where I have to show data in cumulative concatenation style, just like running total by group.

Sample data

Col1    Col2
1       a
1       b
2       c 
2       d
2       e

Expected output:

Col1    Col2
1       a
1       b,a
2       c 
2       d,c
2       e,d,c

The concatenation needs to be broken down by Col1. Any help regarding how to get this result by Oracle SQL will be appreciated.

Upvotes: 0

Views: 2113

Answers (3)

Aleksej
Aleksej

Reputation: 22949

Assuming something on the way you need to order, this can be a solution, based on Hierarchical Queries:

with test as
(
    select 1 as col1,      'a' as col2 from dual union all
    select 1 as col1,      'b' as col2 from dual union all
    select 2 as col1,      'c' as col2 from dual union all
    select 2 as col1,      'd' as col2 from dual union all
    select 2 as col1,      'e' as col2 from dual
 )
select col1, col2
from (
        select col1 AS col1, sys_connect_by_path(col2, ',') AS col2, connect_by_isleaf leaf
        from (
                select row_number() over (order by col1 asc, col2 desc) as num, col1, col2
                from test
             )
        connect by nocycle prior col1 = col1 and prior num = num -1
)
where leaf = 1
order by col1, col2

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191245

I'm not sure you can do this with listagg as it doesn't seem to support windowing clauses. If you're on 11g or higher you can use recursive subquery factoring to achieve your result.

with your_table (col1, col2) as (
            select 1, 'a' from dual
  union all select 1, 'b' from dual
  union all select 2, 'c' from dual
  union all select 2, 'd' from dual
  union all select 2, 'e' from dual
), t as (
  select col1, col2, row_number() over (partition by col1 order by col2) as rn
  from your_table
), r (col1, col2, rn) as (
  select col1, col2, rn
  from t
  where rn = 1
  union all
  select r.col1, t.col2 ||','|| r.col2, t.rn
  from r
  join t on t.col1 = r.col1 and t.rn = r.rn + 1
)
select col1, col2
from r
order by col1, rn;

     COL1 COL2               
---------- --------------------
         1 a                   
         1 b,a                 
         2 c                   
         2 d,c                 
         2 e,d,c

The your_table CTE is just to mimic your base data. The t CTE adds a row_number() analytic column to provide a sequence for the next part. The interesting part is the r recursive CTE. The anchor member starts with the first row (according to rn from the previous CTE). The recursive member then finds the next row (against according to rn) for that col1, and for that it concatenates the current col2 with the previous one, which may itself already be a concatenation.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36097

Try:

WITH d AS (
    select col1, col2, 
           row_number() over (partition by col1 order by col2) as x
    from tab_le
),
d1( col1, col2, x, col22) as (
  SELECT col1, col2, x, col2 col22 FROM d WHERE x = 1
  UNION ALL
  SELECT d.col1, d.col2, d.x, d.col2 || ',' || d1.col22
  FROM d
  JOIN d1 ON (d.col1 = d1.col1 AND d.x = d1.x + 1)
)
SELECT * FROM d1
order by 1,2;

Upvotes: 1

Related Questions