How to make row data comma separated in SQL Server

Question

How to make row data comma-separated in SQL Server?

CREATE TABLE CountryState
(Country Varchar(15), State Varchar(15))
GO

INSERT INTO CountryState VALUES('India','MH')
INSERT INTO CountryState VALUES('India','DL')
INSERT INTO CountryState VALUES('US','NJ')
INSERT INTO CountryState VALUES('US','NY')
GO

SELECT * FROM CountryState

Output:

Country State
----------------------------    
India   MH
India   DL
US  NJ
US  NY

I require output in following format.

Country State
------------------    
India   MH,DL
US          NJ,NY

or http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server ... — a1ex07, Jun 29 '13 at 14:54

score 4 · Accepted Answer · answered Jun 29 '13 at 15:01

There are many ways to do this, but you can use FOR XML PATH to do it in a reasonably concise way.

SELECT DISTINCT Country, 
                STUFF((SELECT ',' + State 
                       FROM CountryState cs 
                       WHERE CountryState.Country = cs.Country
                       FOR XML PATH ('')), 1, 1, '')
FROM CountryState;

An SQLfiddle to test with.

How to make row data comma separated in SQL Server

1 Answers1