Split String based on position and delimiters, Sql Server

Language: Layout:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

CREATE TABLE #Split (ID int, SplitString varchar(450))
Insert into #Split (ID, SplitString) values 
(1, '20:4:10:1:20:3:'),
(2, '20:1:'),
(3, '10:3:'),
(4, '30:4:40:1:50:3:');

WITH CTE1 AS
(
    SELECT  Id,
            CAST(IIF(ItemNumber % 2 = 0, Item, NULL) as int) As Data, 
            IIF(ItemNumber % 2 = 1, Item, NULL) As Name, 
            IIF(ItemNumber % 2 = 0, ItemNumber - 1, ItemNumber) As PairNumber
    FROM  #Split
    CROSS APPLY dbo.DelimitedSplit8K(SplitString, ':')
    WHERE Item IS NOT NULL
    AND Item <> ''
), CTE2 AS
(
    SELECT Id, PairNumber, MAX(Data) As Data, MAX(Name) As Name
    FROM CTE1
    GROUP BY Id, PairNumber
)

, CTEPivot AS
(
    SELECT Id, [10], [20], [30], [40], [50]
    FROM 
    (
    SELECT Id, SUM(Data) As Data, Name
    FROM CTE2 
    GROUP BY Id, Name
    ) D
    PIVOT  
    (  
    AVG(Data) 
    FOR Name IN ([10], [20], [30], [40], [50])  
    ) AS PivotTable
)

SELECT  Id, 
        MAX([10]) As [10], 
        MAX([20]) As [20], 
        MAX([30]) As [30], 
        MAX([40]) As [40], 
        MAX([50]) As [50] 
FROM CTEPivot
GROUP BY Id;

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 4, absolute service time: 0,31 sec, absolute service time: 0,35 sec