Is there a more efficient way to append multiple columns from the same table?

I’d like to return multiple values from the same record, with different columns of the same type appended under one another. I could do multiple joins, but that seems very inefficient because of the multiple table scans.

declare @gameID as VarChar(30)
select @gameID = '20210829-SLNvsPIT-0'

select Vis1ID as VisID, Vis1 as Vis, Home1 as Home, Home1ID as HomeID
from Baseball.dbo.GameLogs
where GameID = @gameID
union
select Vis2ID, Vis2, Home2, Home2ID
from Baseball.dbo.GameLogs
where GameID = @gameID
union
.......
select Vis9ID, Vis9, Home9, Home9ID
from Baseball.dbo.GameLogs
where GameID = @gameID

Returns:

VisID	Vis	Home	HomeID
arenn001	Nolan Arenado	Colin Moran	morac001
badeh001	Harrison Bader	Anthony Alford	alfoa002
carld002	Dylan Carlson	Yoshi Tsutsugo	tsuty001
edmat001	Tommy Edman	Kevin Newman	newmk001
goldp001	Paul Goldschmidt	Ke’Bryan Hayes	hayek001
kim-k001	Kwang Kim	Wil Crowe	croww001
moliy001	Yadier Molina	Jacob Stallings	stalj001
oneit001	Tyler O’Neill	Bryan Reynolds	reynb001
sosae001	Edmundo Sosa	Cole Tucker	tuckc001

This is exactly what I’m looking for, but it’s painfully slow. Is there a better way?

Answer

You need to unpivot each row. This means that you only scan the table once, then break it out into separate rows. You could use UNPIVOT, but CROSS APPLY (VALUES is far more flexible.

DECLARE @gameID varchar(30) = '20210829-SLNvsPIT-0';

SELECT
  v.VisID,
  v.Vis,
  v.Home,
  v.HomeID
FROM dbo.GameLogs gl
CROSS APPLY (VALUES
    (Vis1ID, Vis1, Home1, Home1ID),
    (Vis2ID, Vis2, Home2, Home2ID),
    (Vis3ID, Vis3, Home3, Home3ID),
    (Vis4ID, Vis4, Home4, Home4ID) -- .....
) v(VisID, Vis, Home, HomeID)
WHERE gl.GameID = @gameID;

It goes without saying that your table is seriously denormalized and should be redesigned pronto.