MySQL query to display parent-child data in single column format

[Ethan_19Chess]

I have a database table called location_data structured as follows:

region      |  city
------------+---------------
Tamil Nadu  |  Chennai
Tamil Nadu  |  Coimbatore
Tamil Nadu  |  Madurai
Rajasthan   |  Jaipur
Rajasthan   |  Udaipur

I need to create a MySQL query that displays all the data in a single column. The output should show the region name first, followed by its cities listed underneath, then the next region and its cities.

The expected output is:

Tamil Nadu
Chennai
Coimbatore
Madurai

Rajasthan
Jaipur
Udaipur

Essentially, I want each region followed by its associated cities in a vertical format. How can I construct a MySQL query to achieve this hierarchical layout in one column?

[SkippingLeaf]

I’ve tackled this exact hierarchical display problem before. UNION with proper ordering is your best bet here.

Here’s what works:

SELECT region as display_column, region as sort_region, 0 as sort_order
FROM location_data
UNION
SELECT city as display_column, region as sort_region, 1 as sort_order
FROM location_data
ORDER BY sort_region, sort_order, display_column;

The trick is that sort_order field - 0 for regions, 1 for cities. This forces regions to show up before their cities while keeping everything grouped by region name. The ORDER BY clause does the rest.

This approach has been solid for me across different MySQL versions.

[John_Clever]

I’ve used stored procedures with cursors before, but that’s probably overkill unless you’re dealing with huge datasets. There’s actually a much simpler way using GROUP_CONCAT with some string manipulation:

SELECT REPLACE(
  GROUP_CONCAT(
    CONCAT(region, '\n', 
           GROUP_CONCAT(city ORDER BY city SEPARATOR '\n'))
    ORDER BY region SEPARATOR '\n\n'
  ), '\n', '\r\n'
) as hierarchical_data
FROM location_data 
GROUP BY region;

This dumps everything into one cell with proper line breaks. Yeah, you get it all in a single row instead of separate ones, but it keeps the exact formatting you want. I’ve done this when exporting report data where I needed to preserve the hierarchy in one field.

[AdventurousHiker17]

you can also use a case statement instead of union:

select 
  case when @prev_region != region then region else city end as output
from location_data
cross join (select @prev_region := '') r
order by region, city;

trickier syntax but way faster with large datasets since it skips the union overhead