Split Comma separated string without using cursor or while condition in sql Or Split comma separated string using XQuery

Split Comma separated string without using cursor or while condition in sql.

Split comma separated string using XQuery

Using below Query we can split comma separated string 

DECLARE @CommaSepString VARCHAR(max)

SET @CommaSepString='IND,UK,USA,AST,IND,PAK';

SELECT SplitCountry FROM (SELECT CAST('<A>' +Replace(@CommaSepString,',','</A><A>')+'</A>' AS XML) AS Strings

      )F1

      CROSS APPLY

      (

       SELECT Country.item.value('.','varchar(50)') AS SplitCountry

       FROM F1.Strings.nodes('A') AS Country(item)

      )F2

The out put looks like as below.

If you want to create reversely that mean table rows values to comma separated string look in below link.

http://mastermindsdotnet.blogspot.in/2013/07/create-comma-separated-string-from.html