Split Comma Seperated Values In a Single Cell - SQL Server

Scenario: 

Let us say that I have Medical Product which is manufactured from one or more molecules and data is given by customer in below format:

Product Molecule Comma separated data

Now I want to store data in below format

Comma Separated Values Split Into Rows 

Below is the query which can be used to achieve this:  

WITH Cte AS (

    SELECT Product,

        CAST('<M>' + REPLACE( Molecules,  ',' , '</M><M>') + '</M>' AS XML) AS Molecules

    FROM dbo.ProductMolecules

 )

SELECT

    Product,

    Split.a.value('.', 'VARCHAR(100)') AS Molecules

FROM Cte

CROSS APPLY Molecules.nodes('/M') Split(a);

1) CTE is used to convert comma seperated values into XML format like "<M>X</M><M>Y</M><M>Z</M>"
2) XML holds data in nodes.
3) Cross apply needs Left and Right objects.
4) Your left object is CTE which is holding its data, right object is Molecules column casted to XML data type which you are finding nodes.
5) The data identified in each node will be stored in alias Split(a); Split(a) can be anything like XYZ(AB).
5) Now since you have data splitted internally by XML you are just calling Split.a.Value by again converting nodes data to varchar.6) This takes care of splitting data and hence final result is achieved.