基于首列实现行转列：学生表动态scode列转换需求

这是个很常见的动态透视表需求——既要把行里的scode转成列，还得兼容未来新增的scode值，不能写死列名。下面针对主流关系型数据库给出具体实现，完全匹配你要的输出格式：

先明确前提 #

假设你的表名为student_scores，结构及数据如下：

核心思路是：先给每个学生的记录分配行号（避免同学生的多条记录被合并），再动态生成所有scode对应的列，最后执行透视查询。

MySQL 实现 #

-- 步骤1：给每个学生的记录分配行号（用于拆分多行）
WITH ranked_students AS (
    SELECT 
        name, 
        subject, 
        scode,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY subject) AS row_num
    FROM student_scores
)
-- 步骤2：动态生成所有scode对应的列定义
SET @columns = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN scode = ', scode, ' THEN subject ELSE NULL END) AS `', scode, '`')
) INTO @columns
FROM student_scores;

-- 步骤3：拼接并执行完整的动态SQL
SET @sql = CONCAT(
    'SELECT name, ', @columns, ' 
     FROM ranked_students 
     GROUP BY name, row_num
     ORDER BY name, row_num;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现 #

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

-- 步骤1：动态生成所有scode的列名（带引号避免冲突）
SELECT @columns = STRING_AGG(QUOTENAME(scode), ', ')
FROM (SELECT DISTINCT scode FROM student_scores) AS s;

-- 步骤2：拼接带行号的动态PIVOT语句
SET @sql = N'
WITH ranked_students AS (
    SELECT 
        name, 
        subject, 
        scode,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY subject) AS row_num
    FROM student_scores
)
SELECT name, ' + @columns + N'
FROM ranked_students
PIVOT (
    MAX(subject) FOR scode IN (' + @columns + N')
) AS pvt
ORDER BY name, row_num;';

-- 步骤3：执行动态SQL
EXEC sp_executesql @sql;

PostgreSQL 实现 #

方案1：动态SQL（无需额外扩展） #

-- 步骤1：动态生成列定义
WITH scode_list AS (SELECT DISTINCT scode FROM student_scores)
SELECT string_agg(format('MAX(CASE WHEN scode = %s THEN subject ELSE NULL END) AS "%s"', scode, scode), ', ')
INTO columns_list
FROM scode_list;

-- 步骤2：拼接并执行完整查询
EXECUTE format('
WITH ranked_students AS (
    SELECT 
        name, 
        subject, 
        scode,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY subject) AS row_num
    FROM student_scores
)
SELECT name, %s
FROM ranked_students
GROUP BY name, row_num
ORDER BY name, row_num;', columns_list);

方案2：使用crosstab函数（需安装tablefunc扩展） #

-- 先确保tablefunc扩展已安装
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 动态生成crosstab查询
WITH scode_list AS (SELECT DISTINCT scode FROM student_scores ORDER BY scode),
     col_def AS (SELECT string_agg(format('%s TEXT', quote_ident(scode::TEXT)), ', ') FROM scode_list),
     sql_query AS (
         SELECT format('
             SELECT name, %s FROM crosstab(
                 ''SELECT name, row_num, scode, subject 
                  FROM (
                      SELECT name, subject, scode,
                             ROW_NUMBER() OVER(PARTITION BY name ORDER BY subject) AS row_num
                      FROM student_scores
                  ) AS t
                  ORDER BY name, row_num'',
                 ''SELECT DISTINCT scode FROM student_scores ORDER BY scode''
             ) AS ct(name TEXT, row_num INT, %s);', col_def, col_def) AS query
         FROM col_def
     )
EXECUTE (SELECT query FROM sql_query);

关键说明 #

1. 动态列适配：所有方案都会自动读取当前表中所有唯一的scode值生成列，未来新增scode后无需修改代码，重新执行即可生效。
2. 多行拆分：通过ROW_NUMBER()窗口函数给每个学生的记录分配行号，确保像sam这样有多个同scode记录的情况会拆分成多行，完全匹配你要的输出格式。
3. 空值处理：没有对应scode的科目位置会自动填充NULL，符合需求。

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