SQL Server 2017

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

DECLARE @MATERIAL TABLE (ID int, CODE varchar(30));

INSERT @MATERIAL (ID, CODE) VALUES
(1, 'D3033MBBY'),
(2, 'D3033MBTY'),
(3, '011130-01'),
(4, '011130-04C'),
(5, '021002'),
(6, '021017-B'),
(7, '021134-01'),
(8, '021135-01'),
(9, '021955-01'),
(10, '3LS91101-550'),
(11, 'D3049MBRB'),
(12, 'EF0118'),
(13, 'FV8130'),
(14, 'FY7009'),
(15, 'H05802'),
(16, 'D3033MRTE');

DECLARE @SUBSTITUTE TABLE (ID int, ITEID int, SUBSTITUTECODE varchar(100));

INSERT @SUBSTITUTE (ID, ITEID, SUBSTITUTECODE) VALUES
(5232, 1, '191045762418'),
(5442, 2, '191045762418'),
(6435, 3, '5206432380030'),
(6573, 4, '5206432380030'),
(6582, 5, '5206432357131'),
(6683, 6, '5206432369486'),
(7332, 7, '5206432380610'),
(7482, 8, '5206432380818'),
(7721, 9, '5206432346029'),
(7831, 10, '5205172116350'),
(8034, 11, '191045480992'),
(8184, 12, '4061622759543'),
(8284, 13, '4062058577497'),
(8573, 14, '4064039588089'),
(9438, 15, '4064048672519'),
(9746, 16, '191045762418');

/*  desired 
CODES                            SUBSTITUTECODE
-----------------------------    --------------
D3033MBBY,D3033MBTY,D3033MRTE    191045762418
011130-01,011130-04C             5206432380030
*/
  
  SELECT CODES = STRING_AGG(m.CODE, ','), s.SUBSTITUTECODE
    FROM @SUBSTITUTE AS s
    INNER JOIN @MATERIAL AS m
    ON m.ID = s.ITEID
    GROUP BY s.SUBSTITUTECODE
    HAVING COUNT(*) > 1;

CODES	SUBSTITUTECODE
D3033MRTE,D3033MBBY,D3033MBTY	191045762418
011130-01,011130-04C	5206432380030