MySQL - Query to merge rows in table with duplicate values in on field

Question

I'm quite new on data bases and would be very grateful for some help, I have a database on the following format:

ID     Nbr     Data1     Data2    Data3
1      1       a    
2      1                 b
3      1                          c
4      2       d    
5      2                 e
6      2                          f

And would like to have a way to extract, with a MySQL query, the data on the following format:

Nbr     Data1     Data2    Data3
1       a         b        c
2       d         e        f

I know that is not best practice to have the data on a non normalized format but sadly I can't change the source data.

Grateful for your help!

rekaszeru · Accepted Answer · 2014-05-27 06:12:17Z

5

Insert into newtable 
    select ID,Nbr,max(Data1),max(Data2),max(Data3) from table group by Nbr

Try this and let me know it worked or not

edited May 27, 2014 at 6:12

rekaszeru

19.2k7 gold badges62 silver badges73 bronze badges

answered Jun 13, 2012 at 14:59

user1432124

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

1 Comment

Jeremy Goodell Over a year ago

you have to watch out for nulls

Romil Kumar Jain · Accepted Answer · 2012-06-13 15:08:37Z

4

SELECT Nbr, 
           Max(data1) data1, 
           Max(data2) data2, 
           Max(Data3) data3 
    FROM   table 
    GROUP  BY Nbr

answered Jun 13, 2012 at 15:08

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

Collectives™ on Stack Overflow

MySQL - Query to merge rows in table with duplicate values in on field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related