SQL Server 2014

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

CREATE TABLE dbo.Person(RecordNumber int, FirstName nvarchar(255));

INSERT dbo.Person(RecordNumber, FirstName) VALUES
(167196, N'Mark');

CREATE TABLE dbo.SessionLive(SessionCode char(5), SessionAtLocationID int);

INSERT dbo.SessionLive(SessionCode, SessionAtLocationID) VALUES
('T4182', 8105),
('T4183', 8106),
('T4190', 8113),
('T4179', 8102);

CREATE TABLE dbo.XPersonSchedule(PersonID int, SessionAtLocationID int);

INSERT dbo.XPersonSchedule(PersonID, SessionAtLocationID) VALUES
(167196,      8105),
(167196,      8106),
(167196,      8113),
(167196,      8102);
9 rows affected

/* and then the query you're struggling with */
select Person.RecordNumber as PersonID, Person.FirstName
    , SessionLive.SessionCode
from Person 
join XPersonSchedule on XPersonSchedule.PersonID = RecordNumber
join SessionLive on 
    SessionLive.SessionAtLocationID = XPersonSchedule.SessionAtLocationId
where recordnumber = 167196


PersonID
FirstName
SessionCode


167196
Mark
T4182

167196
Mark
T4183

167196
Mark
T4190

167196
Mark
T4179


/* and maybe some sample desired results

PersonID    FirstName    SessionCode
----------- ----------- -----------
167196      Mark         T4182<crlf>T4183<crlf>T4190<crlf>T4179
*/
SELECT p.RecordNumber, p.FirstName, SessionCodes = STUFF((
  SELECT CONCAT(char(13),char(10),sl.SessionCode)
    FROM dbo.SessionLive AS sl
    INNER JOIN dbo.XPersonSchedule AS xps
      ON sl.SessionAtLocationID = xps.SessionAtLocationID
    WHERE xps.PersonID = p.RecordNumber
    FOR XML PATH(''), TYPE
).value(N'./text()[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.Person AS p
GROUP BY p.RecordNumber, p.FirstName;


RecordNumber
FirstName
SessionCodes


167196
Mark
T4182
T4183
T4190
T4179

PersonID	FirstName	SessionCode
167196	Mark	T4182
167196	Mark	T4183
167196	Mark	T4190
167196	Mark	T4179