如何基于表中现有行动态生成结果集的列？

要实现把每个学生的多行科目成绩转换成一行多组Sub+Marks列的效果，核心思路是给每个学生的科目分配序号，再通过条件聚合将行数据转成列。下面分步骤详细说明：

1. 核心原理与静态实现（科目数量固定时） #

首先我们需要给每个学生的每门课分配唯一行号，用来区分这是第几组Sub/Marks列，再通过CASE语句配合聚合函数提取对应数据。

第一步：给科目添加行号 #

先执行这个查询，给每个学生的科目按顺序编号：

SELECT 
    Name,
    Sub,
    Marks,
    -- 按Name分组，给每个学生的科目排序并编号
    ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sub) AS rn
FROM your_table;

执行后会得到带行号的中间结果：

Name | Sub   | Marks | rn
A    | Hindi | 59    | 1
A    | Eng   | 88    | 2
A    | Maths | 68    | 3

第二步：生成固定数量的多列 #

如果每个学生的科目数量固定（比如都是3门），直接用静态SQL生成对应列即可：

SELECT 
    Name,
    -- 提取第1组的Sub和Marks
    MAX(CASE WHEN rn = 1 THEN Sub END) AS Sub_1,
    MAX(CASE WHEN rn = 1 THEN Marks END) AS Marks_1,
    -- 提取第2组的Sub和Marks
    MAX(CASE WHEN rn = 2 THEN Sub END) AS Sub_2,
    MAX(CASE WHEN rn = 2 THEN Marks END) AS Marks_2,
    -- 提取第3组的Sub和Marks
    MAX(CASE WHEN rn = 3 THEN Sub END) AS Sub_3,
    MAX(CASE WHEN rn = 3 THEN Marks END) AS Marks_3
FROM (
    -- 子查询：带行号的原始数据
    SELECT 
        Name,
        Sub,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sub) AS rn
    FROM your_table
) t
GROUP BY Name;

执行后会得到你想要的结构（注意：数据库不允许重复列名，所以用Sub_1、Marks_1这类命名区分，可按需调整）：

Name | Sub_1 | Marks_1 | Sub_2 | Marks_2 | Sub_3 | Marks_3
A    | Hindi | 59      | Eng   | 88      | Maths | 68

2. 动态生成列（科目数量不固定时） #

如果学生的科目数量不固定，需要自动根据数据生成对应数量的Sub/Marks列，就需要用动态SQL实现。

SQL Server 版本 #

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

-- 动态生成需要的列语句
SELECT @cols = STRING_AGG(
    CONCAT('MAX(CASE WHEN rn = ', rn, ' THEN Sub END) AS Sub', rn, ', MAX(CASE WHEN rn = ', rn, ' THEN Marks END) AS Marks', rn),
    ', '
)
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sub) AS rn
    FROM your_table
) t;

-- 组装完整的查询语句
SET @query = CONCAT('
SELECT Name, ', @cols, '
FROM (
    SELECT 
        Name,
        Sub,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sub) AS rn
    FROM your_table
) t
GROUP BY Name;
');

-- 执行动态SQL
EXEC sp_executesql @query;

MySQL 版本 #

SET @cols = NULL;

-- 动态生成列语句
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN Sub END) AS Sub', rn, ', MAX(CASE WHEN rn = ', rn, ' THEN Marks END) AS Marks', rn
    )
) INTO @cols
FROM (
    SELECT 
        Name,
        Sub,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sub) AS rn
    FROM your_table
) t;

-- 组装查询语句
SET @query = CONCAT('
SELECT Name, ', @cols, '
FROM (
    SELECT 
        Name,
        Sub,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sub) AS rn
    FROM your_table
) t
GROUP BY Name;
');

-- 执行动态SQL
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

重要注意事项 #

• 数据库结果集不允许存在重复列名，所以你期望的重复Sub、Marks列名在实际查询中无法实现，必须给列名添加区分标识（比如序号）。
• 其他数据库（如PostgreSQL）核心逻辑一致，仅字符串聚合函数略有差异（PostgreSQL用STRING_AGG，与SQL Server一致）。

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