SQL Server使用PIVOT行转列将文本值转换为1/0的实现问题

你的代码存在两个核心问题：

• 聚合逻辑错误：你用MAX(ACTION_TYPE)做聚合，返回的是ACTION_TYPE的字符串值而非你需要的1/0标记
• 分组维度不符合需求：PIVOT默认会把所有非聚合、非转列的字段作为分组依据，你输入PIVOT的字段包含了ACTION_DATE，所以会按「员工+日期」分组输出多行，同时没有先对源表去重。

正确实现代码 #

WITH EMPLOYEES(EMPID, EMPNAME, ACTION_DATE,  ACTION_TYPE )AS
(
   SELECT 1234,  'JOHN SMITH', '2021-05-01', 'HIRED' UNION ALL
   SELECT 1234,  'JOHN SMITH'  , '2021-05-01', 'HIRED' UNION ALL
   SELECT 1234,  'JOHN SMITH' ,'2021-07-31', 'TERMINATED' UNION ALL   
   SELECT 1236,  'JANE SMITH', '2021-06-01', 'HIRED' UNION ALL
   SELECT 1236,  'JANE SMITH'  ,'2021-06-15', 'TERMINATED' UNION ALL
   SELECT 1236,  'JANE SMITH' ,'2021-07-31', 'REHIRED' 
),
-- 第一步：对源数据去重，排除重复记录
DISTINCT_EMP AS (
    SELECT DISTINCT EMPID, EMPNAME, ACTION_TYPE FROM EMPLOYEES
),
-- 第二步：给每个行为类型添加标记值1，用于后续聚合判断
EMP_FLAG AS (
    SELECT *, 1 AS ACTION_FLAG FROM DISTINCT_EMP
)
SELECT 
    EMPID,
    EMPNAME,
    -- 不存在的行为类型返回NULL，转成0
    ISNULL([HIRED],0) AS HIRED,
    ISNULL([TERMINATED],0) AS TERMINATED,
    ISNULL([REHIRED],0) AS REHIRED
FROM EMP_FLAG
PIVOT (
    MAX(ACTION_FLAG) -- 有对应行为时取到1，无对应行为返回NULL
    FOR ACTION_TYPE IN ([HIRED],[TERMINATED],[REHIRED])
) AS P1

运行结果 #

如果你需要同时展示日期字段，可以按业务需求新增日期聚合逻辑，比如取每个员工最早/最晚的行为日期，在最终SELECT中添加对应聚合函数即可。

内容的提问来源于stack exchange，提问作者Depth of Field