SQL Dev查询：将多行数据转换为多列（新手求助）

Hey there! As a fellow SQL dev who’s been in your shoes when starting out, I can tell you this is totally feasible—it’s a super common task called "pivoting" data, and since you already know the exact number of students per class (20), it’s even easier to pull off cleanly.

核心思路：数据透视（Pivot） #

The basic idea is to take those repeated rows for a single teacher and turn each unique student entry into a separate column (like student_1, student_2, ..., student_20). The exact syntax varies a little by database, but the core logic stays consistent.

例子1：用支持PIVOT语法的数据库（SQL Server、Oracle、PostgreSQL 11+） #

Let’s assume your original table is called teacher_students with these columns:

• teacher_id (教师唯一ID)
• teacher_name (教师姓名)
• student_name (学生姓名)

First, you’ll need to assign a unique rank to each student per teacher (so we can map them to student_1 through student_20), then use the built-in PIVOT function:

WITH ranked_students AS (
    SELECT 
        teacher_id,
        teacher_name,
        student_name,
        -- 给每个教师的学生按姓名排序，生成1-20的序号
        ROW_NUMBER() OVER (PARTITION BY teacher_id ORDER BY student_name) AS student_rank
    FROM teacher_students
)
SELECT 
    teacher_id,
    teacher_name,
    [1] AS student_1,
    [2] AS student_2,
    -- 依次写出到[20]
    [20] AS student_20
FROM ranked_students
PIVOT (
    MAX(student_name)  -- 这里用MAX/MIN都可以，因为每个rank对应唯一学生
    FOR student_rank IN ([1], [2], ..., [20])  -- 列出所有1到20的序号
) AS pivot_table;

例子2：用不支持PIVOT的数据库（比如MySQL） #

If your database doesn’t have a built-in PIVOT function, you can achieve the same result with CASE statements and GROUP BY:

SELECT 
    teacher_id,
    teacher_name,
    MAX(CASE WHEN student_rank = 1 THEN student_name END) AS student_1,
    MAX(CASE WHEN student_rank = 2 THEN student_name END) AS student_2,
    -- 依次写出到student_rank=20
    MAX(CASE WHEN student_rank = 20 THEN student_name END) AS student_20
FROM (
    SELECT 
        teacher_id,
        teacher_name,
        student_name,
        ROW_NUMBER() OVER (PARTITION BY teacher_id ORDER BY student_name) AS student_rank
    FROM teacher_students
) AS ranked_students
GROUP BY teacher_id, teacher_name;

小提示 #

• The ROW_NUMBER() function sorts students per teacher—you can change the ORDER BY clause to use student_id or another field if you prefer a different sort order.
• If a teacher has fewer than 20 students, the extra columns will show NULL. You can use COALESCE() to replace those with empty strings or a default value, like COALESCE([1], '') AS student_1.

This approach works perfectly for your 20-student class requirement. If you’re using a specific database system (like MySQL, SQL Server, etc.), I can tweak the example to match exactly—but the core idea of ranking students per teacher then pivoting them into columns is universal.

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