(@pString VARCHAR(8000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

-- 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 "zero base" and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) 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 t.N+1

FROM cteTally t

WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

)

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))

FROM cteStart s;

SELECT MAX(CASE DSc.ItemNumber WHEN 1 THEN DSc.Item END) AS Column1,

MAX(CASE DSc.ItemNumber WHEN 2 THEN DSc.Item END) AS Column2,

MAX(CASE DSc.ItemNumber WHEN 3 THEN DSc.Item END) AS Column3,

MAX(CASE DSc.ItemNumber WHEN 4 THEN DSc.Item END) AS Column4--, --You get the idea now

--MAX(CASE DSc.ItemNumber WHEN 1 THEN DSc.Item END) AS Column5,

--MAX(CASE DSc.ItemNumber WHEN 1 THEN DSc.Item END) AS Column6

FROM OPENJSON(@JSON) OJ

CROSS APPLY (VALUES(REPLACE(OJ.[value],'\n9','|')))V([value]) --If you do use a Pipe (|) in your data, use a different character

CROSS APPLY dbo.DelimitedSplit8K_LEAD(V.[value],'|') DSr

CROSS APPLY dbo.DelimitedSplit8K_LEAD(DSr.Item,',') DSc

GROUP BY DSr.ItemNumber

ORDER BY DSr.ItemNumber;