Query that puts into a single column all of the matching values from another table

Question

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!

Thanks, I forgot to mention this is for MSSQL 2014. string-agg() was added in 2017. — Austin, May 17 '22 at 22:46
Wait, `PersonID` in the `Person` table is actually called `RecordNumber`? This seems... quaint. — Aaron Bertrand, May 17 '22 at 23:04

Aaron Bertrand · Accepted Answer · 2022-05-17T23:39:10.397

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

Example db<>fiddle

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;

Example db<>fiddle

I will take ugly!. Nobody sees it but me! It works as I need it to. Going to upgrade mssql once I get past some pressing deadlines. THANK YOU! Don't ask about RecordNunber, lets say 22 years ago I was just learning... It never got changed and now it would be a BIG deal to chnage! — Austin, May 17 '22 at 23:41

Query that puts into a single column all of the matching values from another table

1 Answers1