I have tableA with fields PlayerID, Year, Stat and tableB with fields PlayerID, Year, Stat where both tables will contain several records for each person.
I want to get the maximum year and corresponding stat for a given player from Table A, and the next two years and corresponding stats for the same player from table B.
Please assume the table structure cannot be changed.
I have been able to do this for a specific person with a 3row results table this way:
(SELECT PlayerID, Year, Stat
FROM tableA
WHERE PlayerID = 123
AND Year = (SELECT MAX(Year) FROM tableA where PlayerID = 123))
UNION (SELECT PlayerID, Year, Stat
FROM tableB
WHERE PlayerID = 123
AND year = (SELECT MAX(Year)+1 FROM tableA where PlayerID = 123))
UNION (SELECT PlayerID, Year, Stat
FROM pro
WHERE PlayerID = 123
AND year = (SELECT MAX(Year)+2 FROM tableA where PlayerID = 123))

How would I change this query so that the results lie in a single row:
PlayerID, YearA, StatA, YearB1, StatB1, YearB2, StatB2 where YearB1 and YearB2 are the two years from tableB? 
Finally, how can that code be adjusted to result in a table containing the rows of information for each distinct person from tableA?