title: Convert Comma separated value to rows
body: <p>I have a scenario where I have to convert comma separated values to columns.</p>
  
  <p>Actual:</p>
  
  <pre><code>ID   NAME  Col1       Col2 
   1    A    AB,BC,CD     
   2    B                XY,YZ 
   3    C    DE,FE
  </code></pre>
  
  <p>Expected:</p>
  
  <pre><code>ID  NAME   Col1       Col2 
   1   A      AB 
   1   A      BC 
   1   A      CD 
   2   B                 XY 
   2   B                 YZ 
   3   C      DE 
   3   C      EF 
  </code></pre>
  
  <p>Below is the query</p>
  
  <pre><code>SELECT a.ID,
         a.Name,
         b.splitdata ,
         c.splitdata
  FROM dbo.TABLE1 a CROSS APPLY dbo.fnSplitString(a.COL1,',') AS b
  CROSS APPLY dbo.fnSplitString(a.COL2,',') AS c
  </code></pre>
  
  <p>I am getting result zero. Please let me know if anyone can help me.</p>
  
  <p>Below is the split function</p>
  
  <pre><code>ALTER FUNCTION [dbo].[fnSplitString]   
  (   
      @string NVARCHAR(MAX),   
      @delimiter CHAR(1)   
  )   
  RETURNS @output TABLE(splitdata NVARCHAR(MAX)   
  )   
  BEGIN   
      DECLARE @start INT, @end INT   
      SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)   
      WHILE @start &lt; LEN(@string) + 1 BEGIN   
          IF @end = 0    
              SET @end = LEN(@string) + 1  
  
          INSERT INTO @output (splitdata)    
          VALUES(SUBSTRING(@string, @start, @end - @start))   
          SET @start = @end + 1   
          SET @end = CHARINDEX(@delimiter, @string, @start)  
  
      END   
      RETURN   
  END
  </code></pre>