SQL SERVER COMMA SEPERATED VALUE IN COLUMN QUERY

We have

SELECT * FROM  STATE

SELECT * FROM CITY

Now to Combine Cityname With it’s StateName

Use Follow Query :

SELECT STATENAME,

SUBSTRING ( (SELECT ‘,’ + CITYNAME   FROM CITY WHERE CITY.STATEID = STATE.STATEID  FOR XML PATH(”)) ,2,1000)

AS CITYNANE

FROM STATE