Quick solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TableA TABLE

(

FldA INT NOT NULL

,FldB INT NOT NULL

);

INSERT INTO @TableA(FldA,FldB)

VALUES (3,4)

,(3,5)

,(4,3)

,(5,3)

,(5,4)

,(5,5)

,(6,4)

,(7,4)

,(7,5)

;

DECLARE @TableB TABLE

(

FldC INT NOT NULL

,FldD VARCHAR(20) NOT NULL

);

INSERT INTO @TableB(FldC,FldD)

VALUES (1,'Break Begin')

,(2,'Break End' )

,(3,'Out' )

,(4,'In' )

,(5,'Dept' )

;

SELECT DISTINCT

A.FldA

,STUFF(

(SELECT

CHAR(44) + TB.FldD

FROM @TableA TA

INNER JOIN @TableB TB

ON TA.FldB = TB.FldC

where TA.FldA = A.FldA

ORDER BY TA.FldA,TA.FldB

FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(100)'),1,1,'') AS FldD

FROM @TableA A;

Results

FldA FldD

----------- -------------

3 In,Dept

4 Out

5 Out,In,Dept

6 In

7 In,Dept