create table #finaltable
(
Value nvarchar(50),
TextUnit nvarchar(50),
ValueUnit nvarchar(50)
)
insert into #finaltable(Value)
values
('1.71V, 2.375V, 3.135V'),
('1.89V, 2.625V, 3.465V'),
('1.8V')
; 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