declare @SomeString varchar(50) = '12345678910111213141516'

declare @Pos varchar(50) = '1,1,2,5,4,2'

--For the quirky update we need this data in a table of some sort.
create table #StringOutput
(
	RowNum int,
	ColLength int,
	SumOfLength int
)

insert #StringOutput
select *, null --The null will be updated with the running total
from dbo.DelimitedSplit8K(@pos, ',')

declare @Total int = 0

--this is the quirky update used to store the running total
update #StringOutput
set @Total = SumOfLength = @Total + ColLength
OPTION (MAXDOP 1)

--now that we know where in the original string and the length of each segment we just need to get the correct substring for each segment.
select *, substring(@SomeString, SumOfLength - ColLength + 1, ColLength) as FinalOutput
from #StringOutput

drop table #StringOutput

DECLARE @str VARCHAR(100) = '12345678910111213141516'
DECLARE @pos VARCHAR(50) = '1,1,2,5,4,2'

;WITH 
-- resolve @pos into a table
ResolvedString AS (
	SELECT 
		ItemNumber, 
		Item = CAST(Item AS INT) 
	FROM dbo.DelimitedSplit8K(@pos,',')
)
-- crosstab query
SELECT
	IDENTITY (int, 1, 1) AS Rid,  
	Col1 = MAX(CASE WHEN r.ItemNumber = 1 THEN y.Word ELSE NULL END),
	Col2 = MAX(CASE WHEN r.ItemNumber = 2 THEN y.Word ELSE NULL END),
	Col3 = MAX(CASE WHEN r.ItemNumber = 3 THEN y.Word ELSE NULL END),
	Col4 = MAX(CASE WHEN r.ItemNumber = 4 THEN y.Word ELSE NULL END),
	Col5 = MAX(CASE WHEN r.ItemNumber = 5 THEN y.Word ELSE NULL END),
	Col6 = MAX(CASE WHEN r.ItemNumber = 6 THEN y.Word ELSE NULL END)
INTO #Temp
FROM ResolvedString r
-- use a triangular join to calculate the start position for SUBSTRING()
CROSS APPLY (
	SELECT Startpos = 1+ISNULL(SUM(ir.Item),0) 
	FROM ResolvedString ir 
	WHERE ir.ItemNumber < r.ItemNumber
) x
CROSS APPLY (
	SELECT Word = SUBSTRING(@str,x.Startpos,r.Item)
) y

SELECT * FROM #Temp