Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE testNames > ( > LongName varchar(1000) > ); > > INSERT INTO testNames(LongName) > VALUES('Group Life@LH NA - GRL'), > ('New York@PropFac NA;New York@CasFac NA;New Zealand@LHI - SACUKIAN'), > ('Boston@CasFac NA;New York@CasFac NA'), > ('East/West@LHI - MED'); > GO > > <pre> 4 rows affected > </pre> <!-- --> > SELECT STRING_AGG(a.mail, ';') NewValue > FROM > ( > > SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.LongName) id, LEFT(m.value, CHARINDEX('@', m.value)-1) mail > FROM > ( > SELECT ROW_NUMBER() OVER(ORDER BY LongName) rn, LongName > FROM testNames > ) t CROSS APPLY STRING_SPLIT(LongName, ';') m > ) a > GROUP BY a.rn; > GO > > <pre> > | NewValue | > | :---------------------------- | > | Boston;New York | > | East/West | > | Group Life | > | New York;New York;New Zealand | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ceb79195e1698c97c929746cb92afaa9)*
back to fiddle