Can I get comma separated values from sub query? If not, how to get this done?

Question

I have a table

Create table Country_State_Mapping
(
    Country nvarchar(max),
    State nvarchar(max)
)

With 5 records.

Insert into Country_State_Mapping values('INDIA', 'Maharastra')
Insert into Country_State_Mapping values('INDIA', 'Bengal')
Insert into Country_State_Mapping values('INDIA', 'Karnatak')
Insert into Country_State_Mapping values('USA', 'Alaska')
Insert into Country_State_Mapping values('USA', 'California')

I need to write a SQL query which will have give me 2 records/2 columns as below.

1st column Contry and second AllStates

1 record(2 columns) will be

India and Maharastra,Bengal,Karnatak

2nd

USA and Alaska,California

I tried I like this

select distinct
    OutTable.Country,
    (select State
     from Country_State_Mapping InnerTable
     where InnerTable.Country = OutTable.Country)
from Country_State_Mapping AS OutTable

but did not work...

Answer 1

SELECT Country, AllStates = 
    STUFF((SELECT ', ' + State
           FROM Country_State_Mapping b 
           WHERE b.Country = a.Country
           FOR XML PATH('')), 1, 2, '')
FROM Country_State_Mapping a
GROUP BY Country

Answer 2

This is a bit nasty and potentially slow if the number of records in the Country_State_Mapping table is large but it does get the job done. It uses the recursive feature of Common Table Expressions introduced in SQL 2005.

;WITH Base
AS
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country, [State] DESC) AS CountryRowId,
        ROW_NUMBER() OVER (ORDER BY Country, [State]) AS RowId,
        Country,
        [State]
    FROM Country_State_Mapping
),
Recur
AS
(
    SELECT
        CountryRowId,
        RowId,
        Country,
        [State]
    FROM Base
    WHERE RowId = 1

    UNION ALL

    SELECT
        B.CountryRowId,
        B.RowId,
        B.Country,
        CASE WHEN R.Country <> B.Country THEN B.[State] ELSE R.[State] + ',' + B.[State] END
    FROM Recur R
    INNER JOIN Base B
        ON R.RowId + 1 = B.RowId
)

SELECT *
FROM Recur
WHERE CountryRowId = 1
OPTION (MAXRECURSION 0)--Dangerous