Setting alias to SQL Column based on results

Question

I am attempting to give a column an alias based on results for my game's ranking board. I am struggling to figure out where I should incorporate the line below into my query.

The desired result would be to have the G.Jobcode result rename itself to "Warrior" "Archer" "Mage" depending on the number result.

Select G.Jobcode as WARRIOR where G.Jobcode = 1;

USE DNWORLD

SELECT TOP 25 G.CharacterName, G.JobCode, G.PvPExp, D.PVPWin, D.PVPLose, D.PVPGiveUp
FROM PvPRanking as G
INNER JOIN PVPScores as D
ON G.CharacterID = D.CharacterID

ORDER BY  RANK() OVER (ORDER BY TotalRank ASC )

How many job codes you have? Does jobcode 1 will have Warrior, Archer and Mage? — Jim Macaulay
– Jim Macaulay, Commented Jun 8, 2020 at 8:08
I probably have 90 job codes and each one will represent a different class in the game. "Warrior" "Mage" etc..Yea i know its going to be a pretty fat query. — Pon
– Pon, Commented Jun 8, 2020 at 8:10
On the TOP 25 can be different jobcode, how should the code be decoded to a column-alias? Sure you don't want to decode the value? — Turo
– Turo, Commented Jun 8, 2020 at 8:11
Errr I'm not particularly sure what you're saying, but essentially this is my issue. For example, column result after running the query is = 1 then I want it to literally turn that value '1' to 'warrior' (visually) — Pon
– Pon, Commented Jun 8, 2020 at 8:15
Can't you create a table with your codes and names and join it in the query? — Turo
– Turo, Commented Jun 8, 2020 at 8:25

Salz Mefan · Accepted Answer · 2020-06-17 03:10:37Z

1

Assuming that G.Jobcode is a numerical result, as mentioned by @Turo in the comments, creating a table with 2 columns would make it possible to join the result of the query above on G.Jobcode.

declare @ClassName Table (JobCode int, JobClass varchar(max))

insert into @ClassName (JobCode,JobClass) values
(1,'Warrior')
,(2,'Ranger')
,(3,'Mage')
[...]

Then join this table on the master table with ThisTable.JobCode = G.JobCode

USE DNWORLD

SELECT TOP 25 
 G.CharacterName
 ,G.JobCode
 ,T.JobClass
 ,G.PvPExp
 ,D.PVPWin
 ,D.PVPLose
 ,D.PVPGiveUp

FROM PvPRanking as G
INNER JOIN PVPScores as D ON G.CharacterID = D.CharacterID
LEFT JOIN @ClassName as T ON T.JobCode = G.JobCode

ORDER BY  RANK() OVER (ORDER BY TotalRank ASC )

answered Jun 17, 2020 at 3:10

Salz Mefan

514 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting alias to SQL Column based on results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related