SQLServer中的表进行行转列场景示例

2026-03-29 11:12:02发布 0次浏览

详情描述

一、使用 `PIVOT` 运算符

场景1：统计学生各科成绩

原始表 Scores： | StudentID | Subject | Score | |-----------|----------|-------| | 1 | Math | 90 | | 1 | English | 85 | | 2 | Math | 88 | | 2 | English | 92 |

目标：将科目转为列显示每个学生的成绩。

SELECT StudentID, [Math], [English]
FROM Scores
PIVOT (
    MAX(Score) 
    FOR Subject IN ([Math], [English])
) AS PivotTable;

结果： | StudentID | Math | English | |-----------|------|---------| | 1 | 90 | 85 | | 2 | 88 | 92 |

场景2：动态行转列（列数不固定）

若科目不固定，可以使用动态SQL：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(Subject) 
    FROM Scores 
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = '
SELECT StudentID, ' + @cols + '
FROM Scores
PIVOT (
    MAX(Score) 
    FOR Subject IN (' + @cols + ')
) AS PivotTable';

EXEC sp_executesql @query;

二、使用 `CASE WHEN` 实现

适用于简单场景或SQL Server旧版本：

SELECT 
    StudentID,
    MAX(CASE WHEN Subject = 'Math' THEN Score END) AS Math,
    MAX(CASE WHEN Subject = 'English' THEN Score END) AS English
FROM Scores
GROUP BY StudentID;

三、复杂场景示例

场景3：多指标转列（如同时转科目和学期）

原始表 StudentScores： | StudentID | Subject | Semester | Score | |-----------|----------|----------|-------| | 1 | Math | S1 | 90 | | 1 | Math | S2 | 85 | | 1 | English | S1 | 88 | | 1 | English | S2 | 92 |

目标：按学期和科目同时转列。

SELECT 
    StudentID,
    MAX(CASE WHEN Subject = 'Math' AND Semester = 'S1' THEN Score END) AS Math_S1,
    MAX(CASE WHEN Subject = 'Math' AND Semester = 'S2' THEN Score END) AS Math_S2,
    MAX(CASE WHEN Subject = 'English' AND Semester = 'S1' THEN Score END) AS English_S1,
    MAX(CASE WHEN Subject = 'English' AND Semester = 'S2' THEN Score END) AS English_S2
FROM StudentScores
GROUP BY StudentID;

四、使用 `UNPIVOT`（列转行）

逆操作示例：

-- 假设已有 pivot 结果表 PivotResult
SELECT StudentID, Subject, Score
FROM PivotResult
UNPIVOT (
    Score FOR Subject IN ([Math], [English])
) AS UnpivotTable;

五、实际应用注意事项

聚合函数选择：PIVOT 必须使用聚合函数（如 MAX, SUM, AVG）。 列名处理：动态列名需注意特殊字符，建议使用 QUOTENAME()。性能：大数据量时动态PIVOT可能影响性能，可考虑预先定义列。 兼容性：CASE WHEN 兼容性更好，但代码较长；PIVOT 语法更简洁。

六、完整动态PIVOT模板

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

-- 获取需要转换的列值
SELECT @cols = STRING_AGG(QUOTENAME(Subject), ',')
FROM (SELECT DISTINCT Subject FROM Scores) AS sub;

-- SQL Server 2017以下版本使用：
-- SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Subject) ...), 1, 1, '')

SET @sql = N'
SELECT *
FROM Scores
PIVOT (
    MAX(Score)
    FOR Subject IN (' + @cols + ')
) AS p';

EXEC sp_executesql @sql;

根据具体需求调整聚合函数和条件即可应用于不同场景。