SQL 2016行转列实现：将Type列值转为列头展示对应Quota值

AI 小北

2026-10-6

实现方案

SQL Server 2016支持两种成熟的行转列实现方式，可根据你的使用习惯选择：

方法1：使用PIVOT运算符（语法更简洁）

PIVOT是SQL Server原生支持的行转列专用运算符，代码可读性更高：

SELECT 
    Customer_Name,
    Customer_id,
    ISNULL([type_1], 0) AS type_1,
    ISNULL([type_2], 0) AS type_2,
    ISNULL([type_3], 0) AS type_3,
    ISNULL([type_4], 0) AS type_4
FROM 
(
    -- 此处替换为你原本返回Customer_Name、Customer_id、Type、Quota四列的查询语句
    SELECT Customer_Name, Customer_id, Type, Quota FROM 你的原始查询表
) AS SourceData
PIVOT
(
    -- 聚合函数可按需替换：单条记录用MAX/MIN均可，多条需汇总用SUM/AVG
    SUM(Quota)
    FOR Type IN ([type_1], [type_2], [type_3], [type_4])
) AS PivotResult;

ISNULL 函数用于将无对应Type记录的空值替换为0，符合你的需求
如果同一个客户+同一个Type下存在多条Quota记录，可根据业务需要调整聚合函数

方法2：使用CASE+聚合函数手动实现（灵活度更高）

手动实现的兼容性更强，后续Type取值变动时调整成本更低，且无需额外处理空值：

SELECT
    Customer_Name,
    Customer_id,
    SUM(CASE WHEN Type = 'type_1' THEN Quota ELSE 0 END) AS type_1,
    SUM(CASE WHEN Type = 'type_2' THEN Quota ELSE 0 END) AS type_2,
    SUM(CASE WHEN Type = 'type_3' THEN Quota ELSE 0 END) AS type_3,
    SUM(CASE WHEN Type = 'type_4' THEN Quota ELSE 0 END) AS type_4
FROM 
(
    -- 此处替换为你原本返回Customer_Name、Customer_id、Type、Quota四列的查询语句
    SELECT Customer_Name, Customer_id, Type, Quota FROM 你的原始查询表
) AS SourceData
GROUP BY Customer_Name, Customer_id;