CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
Naveen
Naveen

Reputation: 13

How to concatenate consecutive rows in oracle

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```

Upvotes: 1

Views: 332

Answers (3)

Popeye
Popeye

Reputation: 35900

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!!

Upvotes: 2

Rustam Pulatov
Rustam Pulatov

Reputation: 665

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

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 0

Related Questions