SQL Server中如何将透视列按IsActive拆分为子列生成报表数据

完整可运行代码 #

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '',
    @TotalActiveCol NVARCHAR(MAX) = '',
    @TotalInactiveCol NVARCHAR(MAX) = '';

-- 1. 构造测试表（和原有逻辑一致）
DROP TABLE IF EXISTS #EmployeeDetail
CREATE TABLE #EmployeeDetail(Name nvarchar(50),Department nvarchar(50),IsActive INT)
INSERT INTO #EmployeeDetail(Name,Department,IsActive)
VALUES ( 'John','Account', 1 ), ( 'Harry', 'Store', 0), ( 'Smile', 'HR', 1);

-- 2. 生成带状态后缀的透视列头：格式为[部门_Active]、[部门_Inactive]
SELECT 
    @columns += QUOTENAME(Department + '_Active') + ',' + QUOTENAME(Department + '_Inactive') + ','
FROM    #EmployeeDetail
GROUP BY Department
ORDER BY Department;
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- 3. 生成激活人数总计、未激活人数总计的计算逻辑
SELECT 
    @TotalActiveCol += COALESCE(@TotalActiveCol + '+','') + 'ISNULL(' + QUOTENAME(Department + '_Active') + ',0)',
    @TotalInactiveCol += COALESCE(@TotalInactiveCol + '+','') + 'ISNULL(' + QUOTENAME(Department + '_Inactive') + ',0)'
FROM #EmployeeDetail
GROUP BY Department
ORDER BY Department;

-- 4. 拼接动态SQL
SET @sql ='
SELECT *, 
    ' + @TotalActiveCol + ' AS [Total_Active],
    ' + @TotalInactiveCol + ' AS [Total_Inactive],
    ' + @TotalActiveCol + ' + ' + @TotalInactiveCol + ' AS [Grand Total]
INTO  #temp_MatchesTotal 
FROM   
(
    SELECT 
        Name, 
        -- 拼接部门+状态的组合维度，作为透视的列来源
        DeptStatus = Department + CASE IsActive WHEN 1 THEN ''_Active'' ELSE ''_Inactive'' END,
        Department
    FROM #EmployeeDetail
) t 
PIVOT(
    COUNT(Department) 
    FOR DeptStatus IN ('+ @columns +')
) AS pivot_table;

SELECT * FROM  #temp_MatchesTotal;
DROP TABLE  #temp_MatchesTotal;
'

EXECUTE sp_executesql @sql;

输出结果说明 #

执行上述代码后，将得到如下结构的数据集，可直接映射到报表：

核心改动说明 #

• 透视维度从原来的单个Department改为部门+状态的组合字段，自动生成每个部门下的激活/未激活两个子列
• 总计部分新增了激活人数、未激活人数的单独统计列，满足报表多维度总计需求
• 代码兼容原有动态生成列的逻辑，部门新增/修改无需硬编码调整

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