select SUBSTRING((select ‘,’ + State From State Where State.Cid = Country.Cid
For xml path(”)),2,1000) as statename, Country from Country

Above query generate State name with comma seperator : result as shown below

comma seperator value result

Advertisements