How to reduce multiple rows in a single row and column with SQL Server

In this sample we want to roll up different rows of a table in a single column, with every row value separated by a comma.

I have a simple table like this:

and I want to obtain a table with a single row for each continent, with a column thath list all state of the continent. This is the result i want to obtain:

First concatenate all state in a string with FOR XML command:

SELECT sa.Des_Nazione [Nazioni]
FROM AreaStato as sa
ORDER BY Des_Continente,Des_Nazione
FOR XML PATH('')


Then add a comma between each state:

SELECT ', ' + sa.Des_Nazione
FROM AreaStato as sa
ORDER BY Des_Continente,Des_Nazione
FOR XML PATH('')

With STUFF command we can replace the first comma in the string with a blank char:

SELECT STUFF((SELECT ', ' + sa.Des_Nazione
FROM AreaStato as sa
ORDER BY Des_Continente,Des_Nazione
FOR XML PATH('')), 1, 2, '')

Now simply self-join table and group by continent:
SELECT
ss.Des_Continente as Continente,
STUFF((SELECT ', ' + sa.Des_Nazione
FROM AreaStato as sa
WHERE sa.Des_Continente = ss.Des_Continente
ORDER BY Des_Continente,Des_Nazione
FOR XML PATH('')), 1, 2, '') [Nazioni]
FROM AreaStato as ss
GROUP BY Des_Continente
ORDER BY 1

…. to obtain the result shown above.

There are several ways to obtain this result, but i think this is more simply and fast. The query are tested with SQL Server 2005.