Elegant solution for splitting a Comma Delimited string in SQL

came across this elegant simple solution for splitting a Comma Delimited string in SQL.
This will only work with SQL 2005 and up but it's so simple and elegant i had to share it.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id	AllNames
1	A,B,C
2	A,B
3	X,Y,Z

And here is the expected output -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

Create Sample Data :

-- Create Table for  Sample Data  CREATE TABLE Test  (  ID INT,  AllNames VARCHAR(100)  )  GO  -- Load Sample Data  INSERT INTO test SELECT  1, 'A,B,C' UNION ALL SELECT  2, 'A,B'  UNION ALL SELECT  3, 'X,Y,Z'  GO -- Verify the Sample Data  SELECT Id, AllNames  FROM Test

And here is the query for How to split a comma
delimited string :

;WITH Cte AS (      SELECT          id,          CAST('<M>' + REPLACE( Allnames,  ',' , '</M><M>') + '</M>' AS XML) AS Names      FROM Test  )  SELECT      ID,      Split.a.value('.', 'VARCHAR(100)') AS Names  FROM Cte  CROSS APPLY Names.nodes('/M') Split(a) ---------------------------------------------------------

Acknowledgement : SQL WITH MANGAL