CODEWITHSUNDEEP
mysql
rocks
rocks

Reputation: 190

How to merge two rows into a single row in mysql

The required table should merge two rows from the original table and the description in two rows should be appended into a single required table

Original Table

id  quantity    item_number description unit_price  Total   returns
1   100         MISC SALES  Misc Sales  0.3         30      ABC
2   NULL        NULL        XXXXXX      NULL        NULL    ABC
3   200         MISC SALES  Misc Sales  0.45        90      ABC
4   NULL        NULL        YYYYYY      NULL        NULL    ABC

Required Table

id  quantity    item_number description         unit_price  Total   returns
1   100         MISC SALES  Misc Sales XXXXXX   0.3         30      ABC
2   200         MISC SALES  Misc Sales YYYYYY   0.45        90      ABC

Upvotes: 0

Views: 1497

Answers (1)

shola
shola

Reputation: 704

select round(id/2),quantity,,item_number,GROUP_CONCAT(" " ,description) , unit_price,Total, returns 
from (

 select id,quantity,item_number, description, unit_price,Total, returns from table1 where id%2=1 

UNION

 select id-1,quantity,item_number, description ,unit_price,Total, returns from table1 where id%2=0 
) as temp group by id;

Upvotes: 1

Related Questions