Mysql merge many rows into one with non-empty value

Question

I have a table with data and structure like:

+-----------+-----------+-----------+-----------+
| member_id | bank_name |  bank_ac  | ac_holder |
+-----------+-----------+-----------+-----------+
|        14 |           |   012-000 |           |
|        14 |           |   012-001 |      Andy |
|        14 |   CR Bank |   012-002 |     Jacky |
+-----------+-----------+-----------+-----------+

What i need is to select one entry with all columns filled by the first non-empty value. As:

+-----------+-----------+-----------+-----------+
| member_id | bank_name |  bank_ac  | ac_holder |
+-----------+-----------+-----------+-----------+
|        14 |   CR Bank |   012-000 |     Andy  |
+-----------+-----------+-----------+-----------+

I have no idea how to start. Can someone give me suggestion?

ps: the empty fields are NOT NULL, they are empty string. schema provided here

What is the logic by which Andy gets retained over Jacky? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 28, 2017 at 6:12
thanks for help Tim, I need to have first non-empty value. Because Andy appears earlier than Jacky, I get Andy — Jacky Lau
– Jacky Lau, Commented Sep 28, 2017 at 6:14
Can we assume that there might also be two bank names? Or maybe three? Can we assume that only the bank_name and ac_holder columns will have missing values? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 28, 2017 at 6:19
Sorry, it seems both bank_name, bank_ac and ac_holder will have missing values — Jacky Lau
– Jacky Lau, Commented Sep 28, 2017 at 6:21
I'm not going to answer this question because your data is in a mess, and I see no clear way to rectify your situation. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 28, 2017 at 6:22

M Khalid Junaid · Accepted Answer · 2017-09-28 06:47:40Z

1

You can use some aggregation

select member_id,
min(bank_name) bank_name,
min(bank_ac) bank_ac,
min(ac_holder) ac_holder
from demo
group by member_id

DEMO

If your data contains empty values like '' (note null is not same as '') then you can put case clause in aggregate function to skip these type of values

select member_id, min(case when bank_name is not null and bank_name <> '' then bank_name end) bank_name, min(case when bank_ac is not null and bank_ac <> '' then bank_ac end) bank_ac, min(case when ac_holder is not null and ac_holder <> '' then ac_holder end) ac_holder from demo group by member_id

~~DEMO~~

Another workaround for your question

select member_id,
substring_index(group_concat(case when bank_name is not null and bank_name <> '' then bank_name end order by your_auto_increment_col asc) ,',',1) bank_name,
substring_index(group_concat(case when bank_ac is not null and bank_ac <> '' then bank_ac end  order by your_auto_increment_col asc) ,',',1) bank_ac,
substring_index(group_concat(case when ac_holder is not null and ac_holder <> '' then ac_holder end  order by your_auto_increment_col asc) ,',',1) ac_holder
from demo
group by member_id

DEMO

edited Sep 28, 2017 at 6:47

answered Sep 28, 2017 at 6:10

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jacky Lau Over a year ago

Thanks for help, I tried, but such non-empty fields are not null but "".

Jacky Lau Over a year ago

Hi, thanks for help. Here I try your sql failed under this situation: sqlfiddle.com/#!9/a745b6/1 the bank name should get "CR Bank" rather than "AR Bank"

M Khalid Junaid Over a year ago

@user3711105 answer updated again for provided data set have a look sqlfiddle.com/#!9/a745b6/6

Barmar Over a year ago

@MKhalidJunaid You have no ORDER BY in your GROUP_CONCAT, so the order is unpredictable. You're just selecting a random value from the group.

Barmar Over a year ago

@MKhalidJunaid He mentioned in a comment that there's an auto-increment ID column. You could use that.

|

Collectives™ on Stack Overflow

Mysql merge many rows into one with non-empty value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related