SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test1 (ticket_id TEXT, site_id TEXT);
✓
INSERT INTO test1 (ticket_id, site_id) VALUES
  ('MTN-TT-20220501-01379', 'E7089(Road-Serahisarbaz-Kafebaluch), E7459(Mehrestan), E7460(Mehrestan), T9428(Ashar), W1374(Patakan-(Ashar))'),
  ('MZZ-CD-20220401-01378', 'E7777(Road-Serahisarbaz-Kafebaluch), E8888(Mehrestan), E5555(Mehrestan), T4444(Ashar), W3333(Patakan-(Ashar))');
✓
SELECT * FROM test1;


ticket_id
site_id


MTN-TT-20220501-01379
E7089(Road-Serahisarbaz-Kafebaluch), E7459(Mehrestan), E7460(Mehrestan), T9428(Ashar), W1374(Patakan-(Ashar))

MZZ-CD-20220401-01378
E7777(Road-Serahisarbaz-Kafebaluch), E8888(Mehrestan), E5555(Mehrestan), T4444(Ashar), W3333(Patakan-(Ashar))


CREATE TABLE test2 (ticket_id TEXT, site_id TEXT);
✓
WITH cte AS (
    SELECT ticket_id, '' site_id, site_id || ',' s 
    FROM test1
    UNION ALL 
    SELECT ticket_id,
           SUBSTR(s, 0, INSTR(s, ',')),
           SUBSTR(s, INSTR(s, ',') + 1)
    FROM cte 
    WHERE s <> ''
) 
INSERT INTO test2 (ticket_id, site_id) 
SELECT ticket_id, SUBSTR(TRIM(site_id), 1, 5) 
FROM cte
WHERE site_id <> '';
✓
SELECT * 
FROM test2
ORDER BY ticket_id, site_id;


ticket_id
site_id


MTN-TT-20220501-01379
E7089

MTN-TT-20220501-01379
E7459

MTN-TT-20220501-01379
E7460

MTN-TT-20220501-01379
T9428

MTN-TT-20220501-01379
W1374

MZZ-CD-20220401-01378
E5555

MZZ-CD-20220401-01378
E7777

MZZ-CD-20220401-01378
E8888

MZZ-CD-20220401-01378
T4444

MZZ-CD-20220401-01378
W3333

ticket_id	site_id
MTN-TT-20220501-01379	E7089(Road-Serahisarbaz-Kafebaluch), E7459(Mehrestan), E7460(Mehrestan), T9428(Ashar), W1374(Patakan-(Ashar))
MZZ-CD-20220401-01378	E7777(Road-Serahisarbaz-Kafebaluch), E8888(Mehrestan), E5555(Mehrestan), T4444(Ashar), W3333(Patakan-(Ashar))

ticket_id	site_id
MTN-TT-20220501-01379	E7089
MTN-TT-20220501-01379	E7459
MTN-TT-20220501-01379	E7460
MTN-TT-20220501-01379	T9428
MTN-TT-20220501-01379	W1374
MZZ-CD-20220401-01378	E5555
MZZ-CD-20220401-01378	E7777
MZZ-CD-20220401-01378	E8888
MZZ-CD-20220401-01378	T4444
MZZ-CD-20220401-01378	W3333