Search code examples
sqlms-access

Is it possible to alias column names in pivot table in MS Access 2016 database

I need to change headers that are entries in the lookup table Insurers and the ranking data in PolicyInsurerRankings. This would be easy in a regular query but I need this in pivot query, see below. The output from this query would be insurance company names as column headers concatenated with "- Rank" like "Great West - Ranking", "Manulife - Ranking" etc.

Current output is:

Policy ID | Great West | Manulife
1         |  1         |  2

I need this to be:

Policy ID | Great West - Rank | Manulife - Rank
1         |  1                |  2

Query:

TRANSFORM First(p.ranking) AS FirstOfrankingName 
SELECT p.policyID 
FROM Insurers i 
LEFT JOIN PolicyInsurerRankings p ON i.insName = p.insName 
GROUP BY p.policyID 
PIVOT i.insName;

Is this possible in MS Access 2016?

Thank you in advance

Solution

  • You can have an expression in the PIVOT clause:

    TRANSFORM First(p.ranking) AS FirstOfrankingName
SELECT p.policyID
FROM Insurers AS i LEFT JOIN PolicyInsurerRankings AS p ON i.insName = p.insName
GROUP BY p.policyID
PIVOT i.insName + ' - Rank';