Oracle SQL using while loop to select records

Question

I am trying to write a sql for a table which storing geographic location data. That table has 4 columns - location id, parent id, display name and location type. Parent id will link with another record's location id in the same table and it may be null.

Table Structure

location_id	parent_id	display_name	location_type
1	null	United States	Country
2	1	Texas, United States	States
3	2	Amarillo, Texas	City
4	null	Hong Kong	Country

And I want to generate a report which have 3 columns: locationName,States,Country, the logic is like : while the record have a parent record, put its display name to States/Country Column based on its location type. But I have no idea how to do it using oracle sql.

May someone help me with this?

enter image description here

locationName	States	Country
United States	null	United States
Texas, United States	Texas, United States	United States
Amarillo, Texas	Texas, United States	United States
Hong Kong	null	Hong Kong

The logic will be like :

while(record still have parent id){
    if(location type = "States"){
       States column value = record's display_name
    }
    if(location type = "Country"){
       Country column value = record's display_name
    }
}

Answer 1

It would be easier to do it via recursive query, but in this case, as joining level in 3, it could be done with a series of self-left joins.

Note the subqueries below used. First subquery s is deriving state at level 1. Second subquery c is deriving country at level 2. Third c1 is deriving cities' country , with join with s, which would be at level 3. Then with case statements, you can get the desired result.

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e22239082df9132b0c0790c8d1b8ff0e

select 
display_name as location_name
,case when 
    s.state_id is null and location_type='States' 
        then gl.display_name 
    else s.state_name
end as states
,case when
    c.country_id is null and location_type='Country' and gl.display_name is not null
        then gl.display_name
    when c.country_name is not null 
        then c.country_name
    else c1.country_name end as country
from gl
left join 
    (select location_id as state_id,parent_id as country_id,display_name as state_name
    from gl where location_type='States'
    ) s 
on gl.parent_id=s.state_id
left join 
    (select location_id as country_id,display_name as country_name
    from gl where location_type='Country'
    ) c
on gl.parent_id=c.country_id
left join 
    (select location_id as country_id,display_name as country_name
    from gl where location_type='Country'
    ) c1
on s.country_id=c1.country_id
order by gl.location_id

Answer 2

Here is an approach using a recursive query. Fiddle borrowed from Utsav's answer.

CONNECT_BY_ROOT allows to get attributes of the row used to start the recursion, so we can group by that. CASE returns NULL if the condition is not met which is then ignored by MIN.

SELECT root_display_name,
       MIN(CASE WHEN location_type = 'States'  THEN display_name END) AS states,
       MIN(CASE WHEN location_type = 'Country' THEN display_name END) AS country
FROM
(
  SELECT CONNECT_BY_ROOT(location_id) AS root_location_id,
         CONNECT_BY_ROOT(display_name) AS root_display_name,
         t.*
  FROM t
  CONNECT BY PRIOR parent_id = location_id
)
GROUP BY root_location_id, root_display_name
ORDER BY root_location_id