How to concatenate consecutive rows in oracle

Question

am beginner to oracle. I tried following source code to solve and get as following O/P.But cant, please give some ideas to solve this.

id    product                      sales
---  -------------                --------
   1      Soaps                     1200
   2      Ice cream                 2300
   3      Cool drinks               2500
   4      Tv                        5000
   5      Mobile                   10000
   6      Washing machine          20000```

```O/P
   id    product                   sales
   ---  -------------             --------
   1      Soaps                     1200
   2      Ice cream+Cool drinks     4800
   3      Tv+Mobile                15000 
   6      Washing machine          20000```

How tv and mobile is concatenated ? Is there any mapping of their category? — Popeye, Sep 21 '19 at 07:40

score 1 · Answer 1 · answered Sep 21 '19 at 07:45

There must be a category and product mapping table. Product with category must be mapped to resolve your issue.

Select min(t.id) as id,
Listagg(t.product, ' + ') within group (order by t.id) as product,
Sum(t.sales) as sales
From your_table t
Join mapping_table m
On (m.product = t.product)
Group by m.catrgory;

Cheers!!

score 0 · Answer 2 · answered Sep 21 '19 at 06:44

0

You can use lead() analytic function :

with t1 as
(
select id, 
       concat(concat(product,'+'),lead(product) over (order by id)) as product,
       sales + lead(sales) over (order by id) as sales
  from tab -- your original table
), t2 as
(
select *
  from t1
 where id in (2,4) 
union all
select *
  from tab
 where id in (1,6) 
)
select * 
  from t2
 order by id;

Demo

answered Sep 21 '19 at 06:44

Barbaros Özhan

59,113
10
31
55

Please [accept](https://meta.stackexchange.com/help/accepted-answer) the answer that you think it answers your question best. This way, it's understood that the problem's solution is completed @Naveen – Barbaros Özhan Sep 21 '19 at 10:57
yaa sure bro@Barbaros Özhan – Naveen Sep 21 '19 at 11:21

score 0 · Answer 3 · answered Sep 21 '19 at 07:42

I thing, You need add column for group by. Try please:

WITH temp as (SELECT  1 id, 1 group_id, 'Soaps' str, 1200  as  price FROM dual
UNION ALL
SELECT  2 id, 2, 'Ice cream', 2300  FROM dual
UNION ALL
SELECT  3 id, 2, 'Cool drinks', 2300  FROM dual
UNION ALL
SELECT  4 id, 3, 'Tv', 5000  FROM dual
UNION ALL
SELECT  5 id, 3, 'Mobile', 10000  FROM dual
UNION ALL
SELECT  6 id, 4, 'Washing machine', 20000  FROM dual)
SELECT group_id, LISTAGG(str, ', ')
   WITHIN GROUP (ORDER BY group_id) "str",
   sum(price) price
FROM temp
GROUP BY  group_id

result:

1   Soaps                   1200
2   Cool drinks, Ice cream  4600
3   Mobile, Tv              15000
4   Washing machine         20000

How to concatenate consecutive rows in oracle

3 Answers3