SQL Query to merge several views into one

user_17787506 0
Viewed 80

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.

tsql sql-server sql
2 Answers

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

user_148689970

Try this:

select
    COALESCE(a.title_id,b.title_id,c.title_id),
    CONCAT(
        ISNULL(a.homeTeam,'N/A'),
        ISNULL(b.awayTeam,'N/A'),
        ISNULL(c.homeTeam,'N/A'),
        ) 'Teams',
from TableA a
    full join TableB b on a.title_id = b.title_id
    full join TableC c on c.title_id = a.title_id
where 
    a.homeTeam is not null 
    OR b.awayTeam is not null
    OR c.homeTeam is not null
group by 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.

user_18247810
Related