sorry i confuse
i try that
; with cte as
(
SELECT a.Value
, left (b.value,patindex('%[A-Z]%',b.value)-1) as TextUnit
, right(b.value,patindex('%[0-9]%',b.value)+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
butit give me invalid column a.value