如何在SQL查询中为CREATEDATBRANCH字段添加对应名称列

我希望在CREATEDATBRANCH列旁添加一列，显示对应的名称而非数字，该如何实现？

以下是我当前的SQL查询：

SELECT [PARENTACCOUNT]
  ,[ID]
  ,[TYPE]
  ,[ORIGINALDATE]
  ,[OPENDATE]
  ,[CHARGEOFFDATE]
  ,[CLOSEDATE]
  ,[PURPOSECODE]
  ,BALANCE AS 'Current Balance'  
  ,[ORIGINALBALANCE]
  ,[CHARGEOFFTYPE]
  ,[CHARGEOFFAMOUNT]
  ,[DESCRIPTION]
  ,[MATURITYDATE]
  ,[BRANCH]
  ,[CREATEDBYUSER]
  ,[CREATEDATBRANCH]
FROM 
    [dbo].[LOAN]
WHERE
    OPENDATE BETWEEN '2023-09-01' AND '2023-09-30' 
    AND CLOSEDATE IS NULL; 

我尝试了以下代码，但它并未整合进主查询，而是生成了另一个结果集：

SELECT
    CREATEDATBRANCH,
    CASE
        WHEN CREATEDATBRANCH = 0 THEN 'Special'
        ELSE CAST(LOAN.CREATEDATBRANCH AS varchar(4))
    END AS 'Location'
FROM
    LOAN;

解决方案 #

直接把CASE表达式嵌入到主查询的SELECT列表中，放在CREATEDATBRANCH列的旁边即可，这样就能在同一个结果集里同时显示原数字列和对应的名称列：

SELECT [PARENTACCOUNT]
  ,[ID]
  ,[TYPE]
  ,[ORIGINALDATE]
  ,[OPENDATE]
  ,[CHARGEOFFDATE]
  ,[CLOSEDATE]
  ,[PURPOSECODE]
  ,BALANCE AS 'Current Balance'  
  ,[ORIGINALBALANCE]
  ,[CHARGEOFFTYPE]
  ,[CHARGEOFFAMOUNT]
  ,[DESCRIPTION]
  ,[MATURITYDATE]
  ,[BRANCH]
  ,[CREATEDBYUSER]
  ,[CREATEDATBRANCH]
  -- 新增的Location列，放在CREATEDATBRANCH旁
  ,CASE
        WHEN CREATEDATBRANCH = 0 THEN 'Special'
        ELSE CAST(CREATEDATBRANCH AS varchar(4))
    END AS 'Location'
FROM 
    [dbo].[LOAN]
WHERE
    OPENDATE BETWEEN '2023-09-01' AND '2023-09-30' 
    AND CLOSEDATE IS NULL; 

逻辑说明：

• 当CREATEDATBRANCH的值为0时，Location列显示字符串'Special'
• 其他情况将数字转为字符串类型显示，保持原数字内容

内容的提问来源于stack exchange，提问作者AlwaysLearning