如何在SQL Server的PIVOT中将Gender作为AgeBand的子类别？

要在SQL Server中实现**年龄段（AgeBand）下细分性别（Gender）**的多层级透视，核心思路是将AgeBand、Gender与统计指标（Count/Percent）拼接成唯一的复合列标识，再通过PIVOT完成列转换。以下是具体实现方案：

1. 基础数据示例 #

假设你的源表名为Demographics，结构及测试数据如下：

CREATE TABLE Demographics (
    County VARCHAR(50),
    AgeBand VARCHAR(20),
    Gender VARCHAR(10),
    PersonCount INT,
    PersonPercent DECIMAL(5,2)
);

INSERT INTO Demographics VALUES
('New York', '0-18', 'Female', 120000, 22.5),
('New York', '0-18', 'Male', 125000, 23.4),
('New York', '19-35', 'Female', 150000, 28.1),
('New York', '19-35', 'Male', 145000, 27.2),
('Los Angeles', '0-18', 'Female', 95000, 21.0),
('Los Angeles', '0-18', 'Male', 100000, 22.1);

2. 静态SQL实现（固定维度值） #

如果AgeBand和Gender的取值是固定的，可以直接手动指定透视列：

SELECT 
    County,
    -- 按年龄段+性别+指标的顺序排列列
    [0-18_Female_Count], [0-18_Female_Percent],
    [0-18_Male_Count], [0-18_Male_Percent],
    [19-35_Female_Count], [19-35_Female_Percent],
    [19-35_Male_Count], [19-35_Male_Percent]
FROM (
    -- 第一步：将Count/Percent转为行数据，拼接复合列名
    SELECT 
        County,
        CONCAT(AgeBand, '_', Gender, '_', Metric) AS PivotColumn,
        MetricValue
    FROM Demographics
    UNPIVOT (
        MetricValue FOR Metric IN (PersonCount, PersonPercent)
    ) AS up
) AS src
PIVOT (
    -- 聚合函数用MAX/MIN均可，因为每个组合唯一
    MAX(MetricValue) FOR PivotColumn IN (
        [0-18_Female_Count], [0-18_Female_Percent],
        [0-18_Male_Count], [0-18_Male_Percent],
        [19-35_Female_Count], [19-35_Female_Percent],
        [19-35_Male_Count], [19-35_Male_Percent]
    )
) AS pvt
ORDER BY County;

逻辑说明 #

• UNPIVOT阶段：把原本的PersonCount和PersonPercent两个列拆分成多行，每行对应一个指标类型，同时用CONCAT生成唯一的透视列名（如0-18_Female_Count）。
• PIVOT阶段：将复合列名转成最终的表头，聚合提取对应的值，得到每个区县下、各年龄段分性别的统计数据。

3. 动态SQL实现（适配任意维度值） #

如果AgeBand或Gender的取值会动态变化，用动态SQL自动生成透视列，避免手动维护：

DECLARE @PivotColumns NVARCHAR(MAX);

-- 自动生成所有需要的透视列名
SELECT @PivotColumns = STRING_AGG(QUOTENAME(CONCAT(AgeBand, '_', Gender, '_', Metric)), ', ')
FROM (
    SELECT DISTINCT AgeBand, Gender FROM Demographics
) AS dim
CROSS JOIN (SELECT 'PersonCount' AS Metric UNION SELECT 'PersonPercent') AS metrics;

-- 拼接并执行动态SQL
DECLARE @Sql NVARCHAR(MAX) = CONCAT('
SELECT County, ', @PivotColumns, '
FROM (
    SELECT 
        County,
        CONCAT(AgeBand, ''_'', Gender, ''_'', Metric) AS PivotColumn,
        MetricValue
    FROM Demographics
    UNPIVOT (
        MetricValue FOR Metric IN (PersonCount, PersonPercent)
    ) AS up
) AS src
PIVOT (
    MAX(MetricValue) FOR PivotColumn IN (', @PivotColumns, ')
) AS pvt
ORDER BY County;
');

EXEC sp_executesql @Sql;

注意事项 #

• 如果AgeBand或Gender包含空格、特殊符号，必须用QUOTENAME包裹列名，避免SQL语法错误。
• 确保源表中County+AgeBand+Gender的组合没有重复数据，若有重复需先通过GROUP BY做汇总，再进行透视。
• 聚合函数选择MAX或MIN不影响结果，因为每个复合维度对应的统计值唯一。

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