CODEWITHSUNDEEP
mysqlsqlmerge
YvetteLee
YvetteLee

Reputation: 1090

Is it possible to merge 2 rows in 1 in mysql?

I need to merge multiple rows(2 rows in 1 base on conditions) in my table in one, base on:

same custid and same appdate and price = paid

and have the desire output.

My data now:

tbla
appid   custid    appdate    price   paid
1         1       10/10/20     20    null
2         2       10/10/20     10    null
3         1       11/10/20     30    null
4         3       12/10/20     20    null
5         1       13/10/20     20    null
6         1       10/10/20    null    20
7         2       11/10/20    null    10
8         1       11/10/20    null    20
9         3       12/10/20    null    20
10        1       13/10/20    null    20

Derire output:

tblb
appid   custid    appdate    price    paid
1         1       10/10/20     20       20     => same custid, same appdate, price=paid
2         2       10/10/20     10     null     
3         1       11/10/20     30     null     
4         3       12/10/20     20       20     => same custid, same appdate, price=paid
5         1       13/10/20     20       20     => same custid, same appdate, price=paid
7         2       11/10/20   null       10     
8         1       11/10/20   null       20

Don't bother with appid.I am going to rebuild appid in the end, by creating a new fresh table.

Upvotes: 1

Views: 50

Answers (3)

Ajax1234
Ajax1234

Reputation: 71471

You can use a join with union:

with r as 
  (select t2.appid t2app, t1.appid, t1.custid, t1.appdate, t1.price, t2.paid from test t1 join test t2 on t1.custid = t2.custid and t1.appdate = t2.appdate and t1.price = t2.paid)
select r.appid, r.custid, r.appdate, r.price, r.paid from r
union
select * from test where not exists (select 1 from r where r.t2app = test.appid) and not exists (select 1 from r where r.appid = test.appid);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270693

You seem to want aggregation with a twist:

select min(appid) as appid, custid, appdate,
       max(price) as price, max(paid) as paid
from tbla
group by custid, appdate, coalesce(price, paid);

The twist is treating the price/paid as a single column.

Here is a db<>fiddle.

Note that in your sample data, one of paid or price is always NULL. If there are exceptions, then this code might not work. I would suggest that you ask a new question with appropriate sample data and desired results if that is the case.

Upvotes: 2

GMB
GMB

Reputation: 222622

It looks like simple aggregation does what you want:

select min(appid) as appid, custid, appdate, sum(price) as price, sum(paid) as paid
from mytable
group by custid, appdate

Upvotes: 1

Related Questions