Issue On Values when separate it to value and text?

Transact-SQL
,
23

for my solution i created a real table ... finaltable without #

in your solution .. my code needs to be changed to #finaltable

i changed it .. please see if this works

; with cte as 
(
SELECT   a.Value       
       , left (b.item,patindex('%[A-Z]%',b.item)-1) as TextUnit
	   , right(b.item,patindex('%[0-9]%',b.item)+1) as ValueUnit
FROM #finaltable a  
        cross apply 
     [dbo].[DelimitedSplit8K](a.Value, ',')  b
	 )
SELECT DISTINCT ST2.Value
               , SUBSTRING(( SELECT ','+ST1.TextUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('') ), 2, 1000) TextUnit
	           , SUBSTRING(( SELECT ','+ST1.ValueUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('')), 2, 1000) ValueUnit
FROM cte ST2