Home > Blockchain >  MySQL how to combine not null fields that share the same ID into 1 row per ID
MySQL how to combine not null fields that share the same ID into 1 row per ID

Time:03-11

What is the most sufficient way to combine rows with null sharing the same id:

Having this table:

| UserID | FNAME | LNAME |
|--------|-------|-------|
| 1      | NULL  | NULL  |
| 1      | jhon  | NULL  |
| 1      | NULL  | doe   |
| 2      | NULL  | NULL  |
| 2      | Jarry | NULL  |
| 2      | NULL  | Lauf  |

want to get:
| UserID | FNAME | LNAME |
|--------|-------|-------|
| 1      | jhon  | doe   |
| 2      | Jarry | Lauf  |

CodePudding user response:

Aggregate by user and then take the max of the two name columns:

SELECT UserID, MAX(FNAME) AS FNAME, MAX(LNAME) AS LNAME
FROM yourTable
GROUP BY UserID;
  • Related