I am working with an old database with lots of data. I don't have much access, but I can do queries.
My query is:
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
I have a SQL SELECT result set that looks like this:
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
As you can kindly see, the season_grade is duplicated for each driver.
I would like to remove the season_grade column, and add it as a row for each driver, like this:
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
Would this be possible?
Thanks!
This is really bad practice as you are kinda mixing column meanings, but if you REALLY wanted to do it
SELECT id, driver, event_name, score
FROM (
SELECT id, driver, event_name, CAST(score AS varchar(20)) AS score, 1 AS order_rank
FROM driver AS 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", season_grade, 2 AS order_rank
FROM driver AS 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
) AS data
ORDER BY driver, order_rank
Basically you create distinct view of driver/grades and then union it to the first query with an ordering column, this will ensure it is always at the bottom of the list, then use it as a subquery and order by driver and ranking column.