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

correlated-subquery, sql-server, t-sql

Solution

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

Problem

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...

Original source

Related problems