One way on < 2022:

WITH agg AS
  (
    SELECT Device, 
           a = NULLIF(MAX(a), 0), 
           b = NULLIF(MAX(b), 0)
    FROM
    (
      SELECT Device,
         a = CASE WHEN [key] = 1 THEN TRY_CONVERT(int, value) END,
         b = CASE WHEN [key] = 2 THEN TRY_CONVERT(int, 
             LEFT(value, LEN(value) - PATINDEX('%[0-9]%', 
             REVERSE(value)) + 1)) END
      FROM dbo.[a SQL Server table] 
      CROSS APPLY 
      OPENJSON(CONCAT('["', REPLACE(Device, '_', '","'), '"]"')) AS j
      WHERE j.[key] IN (1,2)
    ) AS src
    GROUP BY Device
  )
  SELECT Device, 
    CONCAT(a, CASE WHEN a > 0 AND b > 0 THEN '-' END, b)
  FROM agg;

• Example db<>fiddle

I am just going to assume you are using SQL Server 2022. As such I am using STRING_SPLIT with the optional ordinal position. If you are on an older version of SQL Server you will need to use a different string splitter. Here is one such example .

Of course I would suggest that part of the issue you are facing is because you have broken 1NF by sticking multiple values inside a single tuple which you now have to break apart.

The only caveat to this is that in your example you only provided a single column and as such there is nothing we can use for an order by to ensure the rows are in the same order as your sample data. I will leave the ordering problem to you, there are many ways that can be solved.

declare @Something table
(
    Device varchar(30)
)

insert @Something values
('DC_003_08TX')
, ('DC_010_04TX')
, ('DC_100_10TX')
, ('DC_004_00TX')
, ('DC_120_119TX')

SELECT max(case when x.ordinal = 2 then try_convert(varchar(10), try_convert(int, x.value)) end) 
    + isnull('-' + nullif(try_convert(varchar(10), try_convert(int, max(case when x.ordinal = 3 then left(x.value, len(x.value) - 2) end))), 0), '')
from @Something s
cross apply string_split(s.Device, '_', 1) x
group by s.Device