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

TableA:

TableB:

TableC:

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

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.

CodePudding user response：

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 a

    UNION ALL

    SELECT 
      b.title_id,
      b.awayTeam
      2
    FROM b

    UNION ALL

    SELECT 
      c.title_id,
      c.homeTeam
      3
    FROM c
) t;

CodePudding user response：

Try this:

select
    a.title_id,
    CONCAT(
        CASE WHEN a.homeTeam is NULL THEN 'N/A' ELSE a.homeTeam END,
        CASE WHEN b.awayTeam is NULL THEN 'N/A' ELSE b.awayTeam END,
        CASE WHEN c.homeTeam is NULL THEN 'N/A' ELSE c.homeTeam END,
        ) 'Teams',
from TableA a
    join TableB b on a.title_id = b.title_id
    join TableC c on c.title_id = a.title_id

As for the DISTINCT title_id, it should be unique by default as I assume it is used as the key in each of the tables.