SQL Server中行转列方法详细讲解

一、使用 PIVOT 运算符（SQL Server 2005+）

1. 基础语法

SELECT [非透视列],
       [透视值1] AS [列名1],
       [透视值2] AS [列名2],
       ...
FROM 
    (SELECT [列] FROM [表]) AS 源表
PIVOT
    (
    聚合函数(被聚合列)
    FOR [透视列] IN ([透视值1], [透视值2], ...)
    ) AS 透视表

2. 示例

假设有以下销售数据：

CREATE TABLE Sales (
    SalesPerson VARCHAR(50),
    Product VARCHAR(50),
    Amount DECIMAL(10,2)
);

INSERT INTO Sales VALUES
('张三', '手机', 1000),
('张三', '电脑', 2000),
('李四', '手机', 1500),
('李四', '平板', 1200),
('王五', '电脑', 1800);

行转列查询：

SELECT SalesPerson,
       ISNULL([手机], 0) AS 手机,
       ISNULL([电脑], 0) AS 电脑,
       ISNULL([平板], 0) AS 平板
FROM 
    (SELECT SalesPerson, Product, Amount 
     FROM Sales) AS SourceTable
PIVOT
    (
    SUM(Amount)
    FOR Product IN ([手机], [电脑], [平板])
    ) AS PivotTable;

二、使用 CASE WHEN 语句（兼容性好）

1. 基本语法

SELECT 
    [分组列],
    SUM(CASE WHEN [条件列] = '值1' THEN [数值列] ELSE 0 END) AS 列名1,
    SUM(CASE WHEN [条件列] = '值2' THEN [数值列] ELSE 0 END) AS 列名2,
    ...
FROM [表名]
GROUP BY [分组列]

2. 示例

SELECT 
    SalesPerson,
    SUM(CASE WHEN Product = '手机' THEN Amount ELSE 0 END) AS 手机,
    SUM(CASE WHEN Product = '电脑' THEN Amount ELSE 0 END) AS 电脑,
    SUM(CASE WHEN Product = '平板' THEN Amount ELSE 0 END) AS 平板
FROM Sales
GROUP BY SalesPerson;

三、动态 PIVOT（列值不固定时）

1. 动态生成SQL

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 获取不重复的列值
SELECT @columns = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM Sales) AS T;

-- 构造动态SQL
SET @sql = N'
SELECT SalesPerson, ' + @columns + '
FROM 
    (SELECT SalesPerson, Product, Amount FROM Sales) AS SourceTable
PIVOT
    (
    SUM(Amount)
    FOR Product IN (' + @columns + ')
    ) AS PivotTable';

-- 执行
EXEC sp_executesql @sql;

2. 兼容旧版本的写法（SQL Server 2017之前）

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @columns = STUFF(
    (SELECT DISTINCT ',' + QUOTENAME(Product)
     FROM Sales
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @sql = 'SELECT SalesPerson, ' + @columns + '
           FROM Sales
           PIVOT (
               SUM(Amount)
               FOR Product IN (' + @columns + ')
           ) AS PivotTable';

EXEC sp_executesql @sql;

四、使用 STRING_AGG + JSON（SQL Server 2016+）

SELECT SalesPerson,
       JSON_VALUE(pivot_data, '$.手机') AS 手机,
       JSON_VALUE(pivot_data, '$.电脑') AS 电脑,
       JSON_VALUE(pivot_data, '$.平板') AS 平板
FROM (
    SELECT SalesPerson,
           (SELECT Product, SUM(Amount) as Amount
            FROM Sales s2
            WHERE s2.SalesPerson = s1.SalesPerson
            GROUP BY Product
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as pivot_data
    FROM Sales s1
    GROUP BY SalesPerson
) AS t;

五、性能优化建议

索引优化：为透视列和分组列创建合适的索引

CREATE INDEX IX_Sales_Product ON Sales(Product, SalesPerson) INCLUDE (Amount);

处理NULL值：使用 ISNULL 或 COALESCE 处理空值

SELECT SalesPerson,
       ISNULL([手机], 0) AS 手机,
       COALESCE([电脑], 0) AS 电脑
FROM ...

数据类型匹配：确保聚合列的数据类型一致

六、选择建议

方法	适用场景	优点	缺点
PIVOT 运算符	列值固定且已知	语法简洁，易于理解	列值必须静态指定
CASE WHEN	简单转换，兼容性好	灵活性高，兼容所有版本	代码较冗长
动态 PIVOT	列值不固定	自动处理变化的列值	需要动态SQL，安全性需注意
JSON 方法	复杂嵌套结构	处理复杂数据结构	性能可能较差

七、实际应用示例

多列透视：

-- 同时透视产品和季度
SELECT SalesPerson, Product,
       ISNULL([Q1], 0) AS Q1,
       ISNULL([Q2], 0) AS Q2,
       ISNULL([Q3], 0) AS Q3,
       ISNULL([Q4], 0) AS Q4
FROM 
    (SELECT SalesPerson, Product, Quarter, Amount 
     FROM SalesWithQuarter) AS SourceTable
PIVOT
    (
    SUM(Amount)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
    ) AS PivotTable;

根据具体需求选择合适的方法，动态PIVOT适合列值不确定的场景，而静态PIVOT或CASE WHEN在列值固定时更加直观。