Create function to split delimited seperated data for all records in the table

Question

I have Input like this-

select ID,FIELD from TABLE

1| A,B,C,D
2|X,Y,Z

Output like this-

SELECT ID,FIELD from TABLE

1|A
1|B
1|C
1|D
2|X
2|Y
2|Z

Could someone please help me as how can I do it in SQL Server 2014 in an easy way ?

Answer 1

You can choose a string splitting function from Aaron Bertrand's Split strings the right way – or the next best way, and use it with cross apply to select the data from your table. For this demonstration I've chosen to go with the XML string split function.
So first, create the function:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

Then all you need to do is something like this:

SELECT ID, Item
FROM TABLE
CROSS APPLY
dbo.SplitStrings_XML(FIELD, ',')

See a live demo on rextester.

Also,you should probably read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

Answer 2

You should XML With CROSS APPLY no need Explicit Function :

SELECT ID,
       split.a.value('.', 'NVARCHAR(MAX)') [FIELD]
FROM
(
    SELECT ID,
           CAST('<M>'+REPLACE([Field], ',', '</M><M>')+'</M>' AS XML) AS String
    FROM #TM
) AS a
CROSS APPLY String.nodes('/M') AS split(a);

Result :

ID FIELD
1  A
1  B
1  C
1  D
2  X
2  Y
2  Z