如何将SQL表中所有值转换为包含多列的单行并创建对应视图

你要做的是固定行数的行转列操作，对于3行固定的场景不需要复杂语法，下面是通用实现方案： #

逻辑原理 #

先给原表的每一行按你需要的顺序编上1、2、3的序号，再把不同序号的行对应的值拆到不同列，最后合并为一行即可。 #

适用所有支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server/Oracle等） #

假设你的原表名为student_marks，创建视图的语句如下：

CREATE VIEW student_marks_wide AS
SELECT
  MAX(CASE WHEN row_num = 1 THEN Student END) AS Student_1,
  MAX(CASE WHEN row_num = 1 THEN Marks END) AS Marks_1,
  MAX(CASE WHEN row_num = 2 THEN Student END) AS Student_2,
  MAX(CASE WHEN row_num = 2 THEN Marks END) AS Marks_2,
  MAX(CASE WHEN row_num = 3 THEN Student END) AS Student_3,
  MAX(CASE WHEN row_num = 3 THEN Marks END) AS Marks_3
FROM (
  -- 内层子查询给每行分配序号，这里按分数降序排序，和你给出的结果顺序一致
  SELECT 
    Student,
    Marks,
    ROW_NUMBER() OVER (ORDER BY Marks DESC) AS row_num
  FROM student_marks
) AS numbered_students;

兼容旧版MySQL（5.7及更早版本，不支持窗口函数） #

可以用自定义变量生成序号：

CREATE VIEW student_marks_wide AS
SELECT
  MAX(CASE WHEN row_num = 1 THEN Student END) AS Student_1,
  MAX(CASE WHEN row_num = 1 THEN Marks END) AS Marks_1,
  MAX(CASE WHEN row_num = 2 THEN Student END) AS Student_2,
  MAX(CASE WHEN row_num = 2 THEN Marks END) AS Marks_2,
  MAX(CASE WHEN row_num = 3 THEN Student END) AS Student_3,
  MAX(CASE WHEN row_num = 3 THEN Marks END) AS Marks_3
FROM (
  SELECT 
    Student,
    Marks,
    @row := @row + 1 AS row_num
  FROM student_marks, (SELECT @row := 0) AS init_var
  ORDER BY Marks DESC
) AS numbered_students;

注意事项 #

• 如果需要调整学生的排序规则，只需要修改内层子查询的ORDER BY字段即可，比如按姓名排序就改成ORDER BY Student ASC。
• 如果后续学生数量会增加，不是固定3个的话，需要用动态SQL生成列，不适合用固定列的视图实现。

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