How to split CSV value in the field into separate rows and connect the rest of the row to it.

[finesoul]

Original article: http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

ALTER FUNCTION [dbo].[Split] ( @Delimiter varchar(5), @List nvarchar(4000) )
RETURNS @TableOfValues table ( RowID smallint IDENTITY(1,1), [Value] varchar(50))
AS
BEGIN
declare @xml xml
set @xml = N'' + replace(@List, @Delimiter,'') + ''

INSERT INTO @TableOfValues([Value])
select t.value('.','nvarchar(4000)') as [delimited items]
from @xml.nodes('//root/r') as a(t)
RETURN
END
go
-- and the most amazing thing is following
select *
from MappingRunningPrintCode mrpc
CROSS APPLY dbo.split(',', mrpc.RunningPrintCodeList) spl

it gaves you following output.