Thank you John,
Sample data below. Many thanks
-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', 'WARTEC/COMGEO'),
('BA', 'WARCAMP/COMGEO'),
('BA', 'WARCAMP/WARTEC/COMGEO'),
('BA', 'MILEAGE/CC/NOTES/CAM/BHC'),
('BA', 'NOTES/MILEAGE/AICAM/BHC'),
('BA', 'CAMPAIGN'),
('BA', 'LAB'),
('BA', 'CAMPAIGN/BDIAG'),
('BA', 'BDIAG'),
('BA', 'BDIAG'),
('BA', 'LAB/TRIAGE'),
('BA', 'MILEAGE/CC/NOTES/CAM'),
('BA', 'TRIAGE')
) d (Name, ref)
SELECT * FROM #SampleData