I need to generate the Sequence Number from both combined data; if I use "n" value from the order by,

then I get for example: 1,2,3,4 from split data and then 1,2 from the column length 40 data (total 6 records for a specific cust).

I need to get 1,2,3,4,5,6 instead of 1,2,3,4,1,2.

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

),

cteTally(n) AS(

SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))

FROM E1, E1 b

)

SELECT cust,

SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ),

n as Seq, <---- I tried adding this but not correct

LEN(s.Item),

LEN(SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ))

FROM tableA a

CROSS APPLY dbo.DelimitedSplit8K( wptext, CHAR(10)) s

JOIN cteTally t ON t.n <= CEILING( LEN( s.Item) / 40.0)

ORDER BY ItemNumber, n