Nesting, grouping Sqlite syntax?

I can't for the life of me figure out this Sqlite syntax.

Our database contains records like:

TX, Austin
OH, Columbus
OH, Columbus
TX, Austin
OH, Cleveland
OH, Dayton
OH, Columbus
TX, Dallas
TX, Houston
TX, Austin
(State-field and a city-field.)

I need output li开发者_StackOverflow社区ke this:

OH: Columbus, Cleveland, Dayton
TX: Dallas, Houston, Austin
(Each state listed once... and all the cities in that state... [edited: each listed once])

What would the SELECT statement(s) look like?

---

You could use group_concat:

select state, group_concat(city)
from YourTable
group by state

In reply to your comment, you can use distinct:

select state, group_concat(distinct city)
                           ^^^^^^^^

---

A slight modification to Andomar's answer seems to do the trick, though I'm not sure that it's really intended to work:

select distinct state, group_concat(distinct city)
from YourTable
group by state