如何实现SQL行转列并标记首次注射的术前术中术后状态？

现有如下数据表：

需求说明 #

将1ST_INJECTION列拆分为Left_1st_Injection和Right_1st_Injection列，并按以下规则标记：

• 首次注射前的记录标记为0
• 首次注射当天的记录标记为1
• 首次注射后的记录标记为2

期望输出 #

已尝试的代码及问题 #

尝试了以下SQL代码，但无法实现标记0和2的逻辑：

IF OBJECT_ID (N 'TEMPDB.DBO. #1st_Injection') IS NOT NULL 
    DROP TABLE #1st_Injection

SELECT 
    ID, 
    Eye, 
    Date, 
    NULL AS 'Left_1st_Injection', 
    '1' AS 'Right_1st_Injection' 
INTO 
    #1st_Injection
FROM 
    My_Table 
WHERE 
    1st_Injection = 1 
    AND Eye = 'Right' 
UNION 
SELECT 
    ID, 
    Eye, 
    Date, 
    '1', 
    NULL
FROM 
    My Table
WHERE 
    1st_Injection = 1
    AND Eye = ' Left '

要实现需求，需先获取每个ID对应眼睛的首次注射日期，再基于该日期判断每条记录的标记值，完整SQL代码如下：

-- 获取每个ID和对应眼睛的首次注射日期
WITH FirstInjectionDates AS (
    SELECT 
        ID,
        Eye,
        MIN(Date) AS First_Injection_Date
    FROM My_Table
    WHERE 1ST_INJECTION = 1
    GROUP BY ID, Eye
)

-- 关联原始表并计算标记值
SELECT 
    t.ID,
    t.Eye,
    t.Date,
    -- 处理左眼标记列
    CASE 
        WHEN t.Eye = 'Left' THEN
            CASE 
                WHEN t.Date < fid.First_Injection_Date THEN 0
                WHEN t.Date = fid.First_Injection_Date THEN 1
                ELSE 2
            END
        ELSE NULL
    END AS Left_1st_Injection,
    -- 处理右眼标记列
    CASE 
        WHEN t.Eye = 'Right' THEN
            CASE 
                WHEN t.Date < fid.First_Injection_Date THEN 0
                WHEN t.Date = fid.First_Injection_Date THEN 1
                ELSE 2
            END
        ELSE NULL
    END AS Right_1st_Injection
FROM My_Table t
LEFT JOIN FirstInjectionDates fid 
    ON t.ID = fid.ID AND t.Eye = fid.Eye
ORDER BY t.Date;

逻辑说明 #

1. CTE部分：通过FirstInjectionDates按ID和眼睛分组，筛选出首次注射（1ST_INJECTION=1）的最早日期，作为该ID对应眼睛的时间分界点。
2. 主查询部分：将原始表与CTE关联，通过嵌套CASE语句完成标记：
   • 先判断当前记录的眼睛类型，只对对应列赋值，另一列置为NULL
   • 再根据当前记录日期与首次注射日期的关系，分别标记0（之前）、1（当天）、2（之后）

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