如何实现带条件的透视表，重复部门值对应新增透视结果行？

核心是先给同一个ID下重复出现的部门生成独立的行编号，再按「ID+行编号」分组做透视，就可以实现同ID同部门每重复一次就新增一条透视记录的效果。

支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等通用写法） #

第一步先给原始数据生成分组行号：

SELECT 
    ID,
    DEPARTMENT,
    -- 按ID和部门分区，同ID同部门每出现一次行号+1
    ROW_NUMBER() OVER(PARTITION BY ID, DEPARTMENT ORDER BY ID) AS rn
FROM original_table

再基于上述结果做透视：

SELECT
    ID,
    MAX(CASE WHEN DEPARTMENT = 'Production' THEN 1 ELSE 0 END) AS Production,
    MAX(CASE WHEN DEPARTMENT = 'IT' THEN 1 ELSE 0 END) AS IT,
    MAX(CASE WHEN DEPARTMENT = 'Sales' THEN 1 ELSE 0 END) AS Sales,
    MAX(CASE WHEN DEPARTMENT = 'Marketing' THEN 1 ELSE 0 END) AS Marketing
FROM (
    SELECT 
        ID,
        DEPARTMENT,
        ROW_NUMBER() OVER(PARTITION BY ID, DEPARTMENT ORDER BY ID) AS rn
    FROM original_table
) t
GROUP BY ID, rn
ORDER BY ID, rn;

老版本MySQL（不支持窗口函数）用变量实现行号： #

SELECT
    ID,
    MAX(CASE WHEN DEPARTMENT = 'Production' THEN 1 ELSE 0 END) AS Production,
    MAX(CASE WHEN DEPARTMENT = 'IT' THEN 1 ELSE 0 END) AS IT,
    MAX(CASE WHEN DEPARTMENT = 'Sales' THEN 1 ELSE 0 END) AS Sales,
    MAX(CASE WHEN DEPARTMENT = 'Marketing' THEN 1 ELSE 0 END) AS Marketing
FROM (
    SELECT 
        ID,
        DEPARTMENT,
        @rn := IF(@prev_id = ID AND @prev_dept = DEPARTMENT, @rn + 1, 1) AS rn,
        @prev_id := ID,
        @prev_dept := DEPARTMENT
    FROM original_table
    CROSS JOIN (SELECT @prev_id := NULL, @prev_dept := NULL, @rn := 1) vars
    ORDER BY ID, DEPARTMENT
) t
GROUP BY ID, rn
ORDER BY ID, rn;

执行上述代码后输出的结果和你要求的透视结果完全一致：
ID=1的两个部门行号都是1，分组后对应第一行记录；ID=2的Sales出现两次，行号分别为1和2，因此会生成两条分组记录，符合重复部门新增行的需求。

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