SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TableA (
  "title_id" INTEGER,
  "homeTeam" VARCHAR(4)
);

INSERT INTO TableA
  ("title_id", "homeTeam")
VALUES
  ('1234', 'WSV'),
  ('5678', 'SSV'),
  ('7890', 'NULL'),
  ('4321', 'SCC');

CREATE TABLE TableB (
  "title_id" INTEGER,
  "awayTeam" VARCHAR(4)
);

INSERT INTO TableB
  ("title_id", "awayTeam")
VALUES
  ('1234', 'SSV'),
  ('5678', 'SFV'),
  ('7890', 'NULL'),
  ('4321', 'KFC');

CREATE TABLE TableC (
  "title_id" INTEGER,
  "homeTeam" VARCHAR(4)
);

INSERT INTO TableC
  ("title_id", "homeTeam")
VALUES
  ('1234', 'SSV'),
  ('5678', 'NULL'),
  ('7890', 'AAB'),
  ('4711', 'BFG');
12 rows affected

SELECT
  t.title_id,
  STRING_AGG(ISNULL(t.team, 'N/A'), ', ') WITHIN GROUP (ORDER BY t.ordering) AS team
FROM (
    SELECT 
      a.title_id,
      a.homeTeam AS team,
      1 AS ordering
    FROM TableA a

    UNION ALL

    SELECT 
      b.title_id,
      b.awayTeam,
      2
    FROM TableB b

    UNION ALL

    SELECT 
      c.title_id,
      c.homeTeam,
      3
    FROM TableC c
) t
GROUP BY
  t.title_id;


title_id
team


1234
WSV, SSV, SSV

4321
SCC, KFC

4711
BFG

5678
SSV, SFV, NULL

7890
NULL, NULL, AAB


WITH t AS (
    SELECT 
      a.title_id,
      a.homeTeam AS team,
      1 AS ordering
    FROM TableA a

    UNION ALL

    SELECT 
      b.title_id,
      b.awayTeam,
      2
    FROM TableB b

    UNION ALL

    SELECT 
      c.title_id,
      c.homeTeam,
      3
    FROM TableC c
)
SELECT
  tOuter.title_id,
  STUFF(tInner.team.value('text()[1]', 'nvarchar(max)'), 1, LEN(', '), '') AS team
FROM (
    SELECT DISTINCT t.title_id
    FROM t
) tOuter
CROSS APPLY (
    SELECT ', ' + ISNULL(t.team, 'N/A')
    FROM t
    WHERE t.title_id = tOuter.title_id
    ORDER BY t.ordering
    FOR XML PATH(''), TYPE
) tInner(team);


title_id
team


1234
 WSV, SSV, SSV

4321
 SCC, KFC

4711
 BFG

5678
 SSV, SFV, NULL

7890
 NULL, NULL, AAB
title_id	team
1234	WSV, SSV, SSV
4321	SCC, KFC
4711	BFG
5678	SSV, SFV, NULL
7890	NULL, NULL, AAB