Convert values in related table to comma-separated list

I have two SQL Server tables:

TableA                  TableB
+------+--------+       +-----+------------+
| aid  |  Name  |       | aid |   Activity |
+------+--------+       +-----+------------+ 
| 1    |   Jim  |       | 1   |   Skiing   |
| 2    |   Jon  |       | 1   |   Surfing  |
| 3    |   Stu  |       | 1   |   Riding   |
| 4    |   Sam  |       | 3   |   Biking   |
| 5    |   Kat  |       | 3   |   Flying   |
+------+--------+       +-----+------------+

I’m trying to the following result where the related activities are in a comma-separated list:

+------+--------+------------------------------+
| aid  |  Name  |   Activity                   |
+------+--------+------------------------------+
| 1    |   Jim  |  Skiing, Surfing, Riding     |
| 2    |   Jon  |  NULL                        |
| 3    |   Stu  |  Biking, Flying              |
| 4    |   Sam  |  NULL                        |
| 5    |   Kat  |  NULL                        |
+------+--------+------------------------------+ 

I tried:

SELECT  aid, Name, STRING_AGG([Activity], ',') AS Activity
FROM    TableA 
        INNER JOIN TableB
        ON TableA.aid = TableB.aid
GROUP BY aid, Name

Can someone help me with this SQL query? Thank you.

Answer

You could use OUTER APPLY to aggregate the string if you’re using SQL Server 2017 or higher.

drop table if exists #TableA;
go
create table #TableA (
  aid            int not null,
  [Name]         varchar(10) not null);

insert #TableA(aid, [Name]) values 
(1, 'Jim'),
(2, 'Jon'),
(3, 'Stu'),
(4, 'Sam'),
(5, 'Kat');

drop table if exists #TableB;
go
create table #TableB (
  aid            int not null,
  [Activity]     varchar(10) not null);

insert #TableB(aid, [Activity]) values 
(1, 'Skiing'),
(1, 'Surfing'),
(1, 'Riding'),
(3, 'Biking'),
(3, 'Flying');

select a.aid, a.[Name], oa.sa
from #TableA a
     outer apply (select string_agg(b.Activity, ', ') sa
                  from #TableB b
                  where a.aid=b.aid) oa;

Name    sa
Jim     Skiing, Surfing, Riding
Jon     NULL
Stu     Biking, Flying
Sam     NULL
Kat     NULL