Is it possible to have SQL append a string to a result given a certain set of conditions?-CodePudding

I have an argument like this in my SQL query:

SELECT game, seasonNumber, leagueNumber, gameTitle
gameMaster AS GameLocation,
COALESCE(TRY_CONVERT(VARCHAR, [gs].[pre_season_rank]), TRY_CONVERT(VARCHAR,[gs].[season_rank]), TRY_CONVERT(VARCHAR, [t].[overall_rank] )) AS Score
FROM GameStats gs
INNER JOIN Totals t

It works, but I need to change it so that the value of gameTitle has 2022_Season_Final_Ranking appended to it IF and ONLY IF Score has the value of [t].[overall_rank]

So the gameTitle column would look like this:

Star Command Multiplayer_2022_Season_Final_Ranking if the Score column gets its data from [t].[overall_rank]

Is there a way to do this in SQL Server?

CodePudding user response：

You mention the query you have shown works, so you can modify it like this:

SELECT game, seasonNumber, leagueNumber, 
CASE WHEN COALESCE(TRY_CONVERT(VARCHAR(10), [gs].[pre_season_rank]), TRY_CONVERT(VARCHAR(10),[gs].[season_rank]), TRY_CONVERT(VARCHAR(10), [t].[overall_rank])) = TRY_CONVERT(VARCHAR(10), [t].[overall_rank]) THEN CONCAT(gameTitle, ' 2022_Season_Final_Ranking')  ELSE gameTitle END AS gameTitle, 
gameMaster AS GameLocation,
COALESCE(TRY_CONVERT(VARCHAR(10), [gs].[pre_season_rank]), TRY_CONVERT(VARCHAR(10),[gs].[season_rank]), TRY_CONVERT(VARCHAR(10), [t].[overall_rank])) AS Score
FROM GameStats gs
INNER JOIN Totals t -- YOU HAVE NOT SHOWN THE JOIN CONDITION - DON'T FORGET IT!

I have made the assumption all the data can fix in a VARCHAR(10). I am not sure why you are doing all the TRY_CONVERTs so I left them in. Also, you never showed the JOIN condition between GameStats and Totals, so make sure you add one.

You have to repeat the long COALESCE expression you use for Score since it is in the SELECT list - there is no shortcut.