How could I handle null fields in a SQL table hierarchy?

Say I have a SQL database table containing location information below. These are hierarchies, each level meant to be nested in the level above (Atlanta is a city in Georgia in country USA). Normally, Munich would be in Bavaria, but for whatever reason there's a gap in the hierarchy:

Location 1	Location 2	Location 3
USA	Georgia	Atlanta
UK	Lancashire	Manchester
Germany	NULL	Munich
Japan	Kanto	Tokyo

I want to make the above table look like this below, moving values up to fill in nulls above them:

Location 1	Location 2	Location 3
USA	Georgia	Atlanta
UK	Lancashire	Manchester
Germany	Munich	NULL or Munich
Japan	Kanto	Tokyo

Could this be done with a view, or would copying to a new table be required?

I've considered using PowerApps (the frontend to read from the database) logic to shift the values, but I wanted to take the computing load off the client onto SQL. If level 2 is blank go to level 3, else add level 2 to list of values. if level 3 is blank, go to level 4, else collect to list of values. So on and so forth.

Solution

Try this:

SELECT location_1, 
  IIF(location_2 IS NULL, location_3, location_2) AS location_2,
  IIF(location_2 IS NULL, CONCAT("NULL or ", location_3), location_3) as 
  location_3 
FROM table1

Also note that it's not a good idea for your columns to contain spaces. Try replacing them for underscores.