Find Cities in Each State - Problem
You are given a table cities that contains state and city information. Each row contains a state name and a city name within that state.
Your task: Write a SQL query to find all cities in each state and combine them into a single comma-separated string.
Requirements:
- Group cities by state
- Concatenate all city names with commas
- Order cities alphabetically within each state
- Order states alphabetically in the final result
Table Schema
cities
| Column Name | Type | Description |
|---|---|---|
state
PK
|
varchar | State name |
city
PK
|
varchar | City name within the state |
Primary Key: (state, city)
Note: Each row represents a unique state-city combination
Input & Output
Example 1 — Multiple States with Multiple Cities
Input Table:
| state | city |
|---|---|
| California | Los Angeles |
| California | San Francisco |
| California | San Diego |
| Texas | Houston |
| Texas | Austin |
| Texas | Dallas |
| New York | New York City |
| New York | Buffalo |
| New York | Rochester |
Output:
| state | cities |
|---|---|
| California | Los Angeles, San Diego, San Francisco |
| New York | Buffalo, New York City, Rochester |
| Texas | Austin, Dallas, Houston |
💡 Note:
The query groups cities by state and concatenates them alphabetically. California gets 3 cities, Texas gets 3 cities, and New York gets 3 cities, all ordered alphabetically within each state.
Example 2 — Single City per State
Input Table:
| state | city |
|---|---|
| Alaska | Anchorage |
| Hawaii | Honolulu |
| Nevada | Las Vegas |
Output:
| state | cities |
|---|---|
| Alaska | Anchorage |
| Hawaii | Honolulu |
| Nevada | Las Vegas |
💡 Note:
When each state has only one city, the STRING_AGG function simply returns that single city name without any commas.
Example 3 — Two Cities per State
Input Table:
| state | city |
|---|---|
| Florida | Tampa |
| Florida | Miami |
| Oregon | Salem |
| Oregon | Portland |
Output:
| state | cities |
|---|---|
| Florida | Miami, Tampa |
| Oregon | Portland, Salem |
💡 Note:
Each state has exactly two cities. The cities are ordered alphabetically within each state: Miami before Tampa for Florida, and Portland before Salem for Oregon.
Constraints
-
1 ≤ number of rows ≤ 1000 -
stateandcitycontain only valid string characters -
(state, city)combination is unique (primary key constraint)
Visualization
Tap to expand
Understanding the Visualization
1
Input
Individual state-city pairs
2
Group & Aggregate
GROUP BY state, STRING_AGG cities
3
Output
One row per state with concatenated cities
Key Takeaway
🎯 Key Insight: Use GROUP BY with STRING_AGG to transform multiple rows into concatenated strings per group
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code