CODEWITHSUNDEEP
mysql
Boby
Boby

Reputation: 1202

How to group by mysql to fill empty row

I have this records

ID Name  key1   key2 key 3
1   A    Key1A   Key2A Key3A 
2   B    Key1B   NULL NULL
3   B    NULL   Key2B Ke3yB
4   C    Key1C   NULL  NULL
5   C    NULL   Key2C  NULL
6   C    NULL   NULL  Key3C

So i need result like this

 Name  key1     key2  key 3
  A    Key1A   Key2A  Key3A 
  B    Key1B   Key2B  Key3B
  C    Key1C   Key2C  Key3C

Is it possible to achieve it ? thanks in advance

Upvotes: 1

Views: 152

Answers (2)

James
James

Reputation: 1829

Sharing my ideas on this, you can use group_concatand if there are duplicate values distinct will give a result in unique.

SELECT 
    `Name`,
    GROUP_CONCAT(DISTINCT key1),
    GROUP_CONCAT(DISTINCT key2),
    GROUP_CONCAT(DISTINCT key3)
FROM
    your_table
GROUP BY `Name`;

Note: If you have multiple value for a Name in a singlekey(n) column then it will result more than one value for that column.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Aggregate by name and take the max of each other column:

SELECT
    Name,
    MAX(key1) AS key1,
    MAX(key2) AS key2,
    MAX(key3) AS key3
FROM yourTable
GROUP BY
    Name;

This works because the MAX function, when used to aggregate, ignores NULL values. So, if a given column has a number of records only one of which is not NULL, that value would be retained after aggregating.

Upvotes: 2

Related Questions