Pivot rows into columns

[malaygal]

I have a sql table with these data
[pre]
Year Country Ranking Category
2018 Ghana 1 Swimming
2018 Sweden 2 Swimming
2018 Costa Rica 3 Swimming
2018 Jordan 1 Sprint
2018 Thailand 2 Sprint
2018 Finland 3 Sprint
2018 Myanmar 1 Boxing
2018 Peru 2 Boxing
2018 Belgium 3 Boxing
2017 Nigeria 1 Swimming
2017 Philippines 2 Swimming
2017 Haiti 3 Swimming
2017 Netherlands 1 Sprint
2017 Macedonia 2 Sprint
2017 Kuwait 3 Sprint
2017 Malaysia 1 Boxing
2017 New Zealand 2 Boxing
2017 Palau 3 Boxing
[/pre]

and would like to create a report that looks like this:

[pre]
Category Ranking 2017 2018
Swimming 1 Nigeria Ghana
Swimming 2 Philippines Sweden
Swimming 3 Haiti Costa Rica
Sprint 1 Netherlands Jordan
Sprint 2 Macedonia Thailand
Sprint 3 Kuwait Finland
Boxing 1 Malaysia Myanmar
Boxing 2 New Zealand Peru
Boxing 3 Palau Belgium
[/pre]

I have googled examples of pivot, but all had to aggregate a column which I do not see being applicable in my case.
Any help will be greatly appreciated.

 

[dhookom]

One way to do this doesn't involve PIVOT and works with your limited data set. There are some assumptions regarding the full set of results for each year for each category.

SELECT A.Category , A.Ranking, A.Country [2017], B.country [2018]
FROM MalayGal A JOIN malaygal B
 ON A.Category = B.Category 
 AND A.Ranking = B.Ranking 
 AND A.year = 2017
 AND B.Year = 2018

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016