SQL Query to merge several views into one

I was trying to create a new view out of 3 or 4 other views/tables.

TableA:

title_id	homeTeam
1234	WSV
5678	SSV
7890	NULL
4321	SCC

TableB:

title_id	awayTeam
1234	SSV
5678	SFV
7890	NULL
4321	KFC

TableC:

title_id	homeTeam
1234	SSV
5678	NULL
7890	AAB
4711	BFG

I would like to generate a new view out of those three which looks like:

title_id	Teams
1234	WSV, SSV, SSV
5678	SSV, SFV, N/A
7890	N/A, N/A, AAB
4321	SCC, KFC, N/A
4711	N/A, N/A, BFG

As you can see, NULL should be renamed to N/A, as well if id doesn’t exist in one of the other tables. And I would like to get DISTINCT title_id.

@DaleK sure, i tried it like this:

select tableA.title_id, ISNULL(tableA.homeTeam, ‘N/A’) + ‘,’ + ISNULL(tableB.awayTeam, ‘N/A’) + ‘,’ + ISNULL(tableC.homeTeam, ‘N/A’) as Teams from tableA, tableB, tableC;

This leads into an neverending query for my tables which has each more than 300k rows.

Next i tried join:

select tableA.title_id, ISNULL(tableA.homeTeam, ‘N/A’) + ‘,’ + ISNULL(tableB.awayTeam, ‘N/A’) + ‘,’ + ISNULL(tableC.homeTeam, ‘N/A’) as Teams from tableA FULL JOIN tableB ON tableB.title_id = tableA.title_id FULL JOIN tableC ON tableC.title_id = tableA.tile_id

But to be honest i wasnt sure about the number of rows.

Answer

You can UNION ALL the tables together, then use string aggregation

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;

For SQL Server 2016 and earlier, you have to use the FOR XML method, and it’s less efificent as you have to query the tables multiple times

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);

db<>fiddle