I need a query that puts into a single column all of the matching values from another table.
I have three tables that track schedules for people. A person table a Sessions table and a xref table of the schedules.
Person Table
---------------------
PersonID FirstName
--------- ---------
167196 Mark
SessionLive Table
-------------------------
SessionCode SessionAtLocationID
----------- -------------------
T4182 8105
T4183 8106
T4190 8113
T4179 8102
XPersonSchedule Table of the Persons schedule
-------------------------------------------------
PersonID SessionAtLocationID
----------- -------------------
167196 8105
167196 8106
167196 8113
167196 8102
This select:
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
Gives me this:
PersonID FirstName SessionCode
----------- ----------- ----------
167196 Mark T4182
167196 Mark T4183
167196 Mark T4190
167196 Mark T4179
I need a select that gives me this instead. One row for each person with their sessions in one column cr/lf separated.
PersonID FirstName SessionCode
----------- ----------- -----------
167196 Mark T4182<crlf>T4183<crlf>T4190<crlf>T4179
Please! Thank you!
This is a really ugly solution in old life-support versions of SQL Server:
SELECT PersonID = 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;
Output:
PersonID | FirstName | SessionCodes |
---|---|---|
167196 | Mark | T4182 T4183 T4190 T4179 |
This is a little simpler in newer versions (for background, see String Aggregation Over the Years in SQL Server):
SELECT PersonID = p.RecordNumber, p.FirstName,
SessionCodes = STRING_AGG(sl.SessionCode, char(13)+char(10))
FROM dbo.SessionLive AS sl
INNER JOIN dbo.XPersonSchedule AS xps
ON sl.SessionAtLocationID = xps.SessionAtLocationID
INNER JOIN dbo.Person AS p
ON xps.PersonID = p.RecordNumber
GROUP BY p.RecordNumber, p.FirstName;