Mysql merge multiple records with same id ignoring null fields

Question

I'm looking to merge a number of records with the same Id into one record per Id. I have had to use pivot tables to get the data I need from multiple other tables and into an interim table that looks something like this:

ID |Data1|Data2|Data3|Data4
1   asdf  NULL  NULL  NULL
1   NULL  blah  NULL  NULL
1   NULL  NULL  this  NULL
1   NULL  NULL  NULL  data
2   NULL  funk  NULL  NULL
2   NULL  NULL  jazz  NULL

And I would like to find a way to merge all records with the same Id into a new record in a new table that looks like this:

ID |Data1|Data2|Data3|Data4
1   asdf  blah  this  data
2   NULL  funk  jazz  NULL

I have looked through multiple other similar questions on here and other websites that seem to be dealing with numerical values in the fields such as this one (Merge multiple rows with same ID into one row). These do not have the desired effect. Any help would be appreciated.

score 2 · Answer 1 · edited Oct 04 '18 at 14:33

2

Use MAX() with GROUP BY Clause :

SELECT t.ID, 
       MAX(t.Data1), 
       MAX(t.Data2), 
       MAX(t.Data3), 
       MAX(t.Data4) 
FROM table AS t
GROUP BY t.ID;

edited Oct 04 '18 at 14:33

Madhur Bhaiya

28,155
10
49
57

answered Oct 04 '18 at 14:21

Yogesh Sharma

49,870
5
26
52

1

Minor: Why alias if you don't use it ? – sagi Oct 04 '18 at 14:23
1

Thank you, I had tried this previously but I was incorrectly using aliases on some of the field titles which was then messing everything else up! All working now :) – BobbyQ Oct 04 '18 at 15:20

score 1 · Answer 2 · answered Oct 04 '18 at 14:22

If there is only 1 value for each column (which seems like it from your data sample) , then you can use aggregation methods:

SELECT t.id ,
       max(t.data1) as data1,
       max(t.data2) as data2,
       max(t.data3) as data3,
       max(t.data3) as data3
FROM YourTable t
GROUP BY t.id

Mysql merge multiple records with same id ignoring null fields

2 Answers2