如何将SQL查询结果中重复的season_grade列转为行数据？

问题背景 #

现有只读权限的旧数据库，原查询返回结果中每个司机的season_grade重复出现，需求是移除该列，将每个司机的赛季评分转为单独一行，格式如示例所示。

原查询语句 #

SELECT id, driver, event_name, score, season_grade
FROM driver d
INNER JOIN events e ON d.driverId = e.driverId
INNER JOIN eventscore es ON e.eventId = es.eventId
INNER JOIN seasonsummary ss ON d.driverId = ss.driverId

原查询结果 #

id     driver     event_name    score   season_grade          
---------------------------------------------------------------------
1      Smith      California    23      A
1      Smith      London        11      A
1      Smith      Boise         76      A
2      Satoru     Paris         5       C
2      Satoru     Dubai         6       C
2      Satoru     Havana        14      C
3      Qura       Tokyo         21      B
3      Qura       Miami         45      B
3      Qura       Sidney        18      B

目标格式 #

id     driver     event_name      score             
--------------------------------------
1      Smith      California      23     
1      Smith      London          11     
1      Smith      Boise           76  
1      Smith      Season Score    A   
2      Satoru     Paris           5      
2      Satoru     Dubai           6      
2      Satoru     Havana          14
2      Satoru     Season Score    C
3      Qura       Tokyo           21     
3      Qura       Miami           45     
3      Qura       Sidney          18
3      Qura       Season Score    B    

解决方案 #

可以通过UNION ALL合并两类数据实现需求：一类是原有的赛事数据（移除season_grade），另一类是每个司机的赛季评分行。具体SQL语句如下：

-- 第一部分：原有赛事数据，不含赛季评分
SELECT id, driver, event_name, CAST(score AS VARCHAR) AS score
FROM driver d
INNER JOIN events e ON d.driverId = e.driverId
INNER JOIN eventscore es ON e.eventId = es.eventId
INNER JOIN seasonsummary ss ON d.driverId = ss.driverId

UNION ALL

-- 第二部分：每个司机的赛季评分行（去重避免重复生成）
SELECT DISTINCT id, driver, 'Season Score' AS event_name, season_grade AS score
FROM driver d
INNER JOIN seasonsummary ss ON d.driverId = ss.driverId

-- 排序确保每个司机的赛事行在前，赛季评分行在后
ORDER BY id, CASE WHEN event_name = 'Season Score' THEN 1 ELSE 0 END

语句说明 #

1. 第一部分：保留原查询的赛事数据，移除season_grade列，将score转为字符串类型（避免和第二部分的字符串类型season_grade出现类型不兼容问题）。
2. 第二部分：用DISTINCT确保每个司机只生成一条赛季评分行，将event_name设为固定值Season Score，score列填充season_grade的值。
3. 排序规则：通过CASE表达式让赛事行（非Season Score）排在前面，赛季评分行自动落在每个司机的最后。

注意：如果数据库中score和season_grade类型本身兼容（比如都是字符串），可以省略CAST(score AS VARCHAR)转换。

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