CODEWITHSUNDEEP
mysqlsql
Nomiluks
Nomiluks

Reputation: 2092

How to Merge rows in mysql?

In MySQL I want to merge information into a single row where we have the following scenario:

Data

uid |date       |cl      |m      |

149 |2017-03-19 |NULL    |14.2857|
149 |2017-03-19 |46.1538 |NULL   |

In above table I have information for the same uid and date and I want to merge them into a single row as given below.

Merged Information

uid |date       |cl      |m      |

149 |2017-03-19 |46.1538 |14.2857|

Upvotes: 2

Views: 255

Answers (2)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

You can do the following :

SELECT      t.uid,
            t.date,
            MAX(t.cl) AS cl,
            MAX(t.m) AS m
FROM        TableName t
GROUP BY    t.date,
            t.uid

You can see this here -> http://rextester.com/OROY60007

EDIT : If you want to represent NULL as 0, you can make use of the ISNULL function in SQL. This will lead to a very minor change in the current query :

SELECT      t.uid,
            t.date,
            ISNULL(MAX(t.cl), 0) AS cl,
            ISNULL(MAX(t.m), 0) AS m
FROM        TableName t
GROUP BY    t.date,
            t.uid

Hope this helps!!!

Upvotes: 1

Matt
Matt

Reputation: 15071

Use a GROUP BY.

Assuming your date is unique as well as your uid then:

SELECT uid, date, MAX(cl) AS cl, MAX(m) AS m
FROM yourtable
GROUP BY uid, date

If not then:

SELECT uid, MAX(date), MAX(cl) AS cl, MAX(m) AS m
FROM yourtable
GROUP BY uid

Adjust MIN/MAX to suite your output needs.

Upvotes: 0

Related Questions