sql group by with different lines-CodePudding

I have a table like this:

Name | City1 | City2 | City3 |
------------------------------
Nike | Oslo  |       |       |
Nike |       | Oran  |       |
Nike |       |       | Riga  |

I want the output to be:

Name | City1 | City2 | City3 |
------------------------------
Nike | Oslo  | Oran  | Riga  |

Looks simple but I can't figure out how? I would be very grateful for help

CodePudding user response：

You can use the following solution:

SELECT Name,
  MAX(City1) City1,
  MAX(City2) City2, 
  MAX(City3) City3
FROM table_name
GROUP BY Name

demo

CodePudding user response：

Use STRING_AGG or GROUP_CONCAT depends on which DB you use if there are multiple values in the same column for the same key.

SELECT Name,
  STRING_AGG(City1, ',') City1,
  STRING_AGG(City2, ',') City2, 
  STRING_AGG(City3, ',') City3
FROM table_name
GROUP BY Name