I have an example table like this and I want to sort the entry column which is region, country, city value from column into row with alphabetical order in orderID column with SQL query

Expected output :

CodePudding user response：

We use inner join to attach the countries to the continents and another inner join to attach the cities to the countries.

select   list, region, country, city
from     (
         select   t.list
                 ,t2.entry as region
                 ,t3.entry as country
                 ,case when t.entry != t3.entry then t.entry end as city
                 ,case when count(*) over(partition by t3.entry) > 1 and t3.entry = t.entry then 1 end as mrk
                 ,t.orderID
         from     t join t t2 on t2.orderID in(left(t.orderID, 2)) and t.entry != t2.entry join t t3 on t3.orderID in(left(t.orderID, 3)) 
         ) t
where    mrk is null
order by t.orderID

Fiddle