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:

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

CodePudding user response：

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.