How do I assign dynamic aliases?

See more:

SQL

SSMS

I have a SQL Query that I use regularly to extract data from any one of multiple databases of the same structure. The catch is that with respects to account servicers, sometimes certain columns have a different meaning (example: one db might have servicer1 as csr and servicer2 as account manager, the other may assign those same roles to 5 & 6). The UI dynamically adjusts those titles on it's own, but I can only bring in the generic servicer numbers because their meanings may change based on user defined info elsewhere in the db. I was wondering if there was a way to assign a select statement to my aliases based on table data that I can join from another part of the db so that my data is richer? I am using SSMS.

What I have tried:

I know I can't simply put:

SR1 AS (SELECT Title FROM SRRoles WHERE NumericalID=1)

But maybe someone can navigate me to an answer