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