TEST, Sql Server - rextester

Language: Layout:

CREATE TABLE Table1
    (SEQ INTEGER,OldSPC varchar(MAX), OLD_FREQ varchar(MAX), NEW_SPC varchar(MAX), NEW_FREQ varchar(MAX))
;
    
INSERT INTO Table1
    (SEQ,OldSPC, OLD_FREQ, NEW_SPC, NEW_FREQ)
VALUES
    (1,'ADH,BAP', '7,9', 'ADH,BAP', '7,8'),
    (2,'NOT REQ', 'NOT REQ', 'ADH,BAP', '7,8');
--    ('','','','');

;with tmp(SEQ,OldSPCItem,OldSPC,OLD_FREQ_item,OLD_FREQ) as (
select SEQ, LEFT(OldSPC, CHARINDEX(',',OldSPC+',')-1),
    STUFF(OldSPC, 1, CHARINDEX(',',OldSPC+','), ''),
    
    LEFT(OLD_FREQ, CHARINDEX(',',OLD_FREQ+',')-1),
    STUFF(OLD_FREQ, 1, CHARINDEX(',',OLD_FREQ+','), '')
from table1
union all
select SEQ,  LEFT(OldSPC, CHARINDEX(',',OldSPC+',')-1),
    STUFF(OldSPC, 1, CHARINDEX(',',OldSPC+','), ''),
        LEFT(OLD_FREQ, CHARINDEX(',',OLD_FREQ+',')-1),
    STUFF(OLD_FREQ, 1, CHARINDEX(',',OLD_FREQ+','), '')
from tmp
where OldSPC > ''
)
select seq,OldSPCItem , OLD_FREQ_item
from tmp
order by SEQ;

View schema
Execution time: 0,02 sec, rows selected: 5, rows affected: 2, absolute service time: 0,17 sec