Oracle数据库表关联与行转列查询求助：未得到预期结果

现有表结构与数据 #

学生语言选择表（Student_Langs） #

语言对照表（Lang_table） #

预期结果 #

解决方案SQL语句 #

方法1：条件聚合（灵活适配语言数量变化） #

SELECT
    s.RollNum,
    MAX(CASE WHEN l.lang = 'lang1' THEN 'Y' ELSE 'N' END) AS lang1,
    MAX(CASE WHEN l.lang = 'lang2' THEN 'Y' ELSE 'N' END) AS lang2,
    MAX(CASE WHEN l.lang = 'lang3' THEN 'Y' ELSE 'N' END) AS lang3,
    MAX(CASE WHEN l.lang = 'lang4' THEN 'Y' ELSE 'N' END) AS lang4,
    MAX(CASE WHEN l.lang = 'lang5' THEN 'Y' ELSE 'N' END) AS lang5
FROM
    (SELECT DISTINCT RollNum FROM Student_Langs) s
CROSS JOIN Lang_table l
LEFT JOIN Student_Langs sl ON s.RollNum = sl.RollNum AND l.lang_id = sl.lang_opted
GROUP BY s.RollNum
ORDER BY s.RollNum;

方法2：Oracle PIVOT语法（适合固定列场景） #

WITH Student_Lang_Flags AS (
    SELECT
        sl.RollNum,
        l.lang,
        'Y' AS flag
    FROM Student_Langs sl
    JOIN Lang_table l ON sl.lang_opted = l.lang_id
)
SELECT
    RollNum,
    COALESCE(lang1, 'N') AS lang1,
    COALESCE(lang2, 'N') AS lang2,
    COALESCE(lang3, 'N') AS lang3,
    COALESCE(lang4, 'N') AS lang4,
    COALESCE(lang5, 'N') AS lang5
FROM Student_Lang_Flags
PIVOT (
    MAX(flag)
    FOR lang IN ('lang1' AS lang1, 'lang2' AS lang2, 'lang3' AS lang3, 'lang4' AS lang4, 'lang5' AS lang5)
)
ORDER BY RollNum;

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