Create new row with data from another row/column

Question

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!

Answer 1

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.

Answer 2

You can use a UNION ALL embedded in a CROSS APPLY to achieve your result. This combines the results of the two independent join branches. Since you have mixed data types, you need to apply a conversion.

If you wish to place the season_grade values after the other scores, you can add a fixed sortOrder value to each UNION branch and reference that in the ORDER BY.

SELECT d.id, d.driver, u.event_name, u.score
FROM driver d
CROSS APPLY (
    SELECT e.event_name, CAST(es.score AS VARCHAR(100)) AS score, 1 as sortOrder
    FROM events e
    INNER JOIN eventscore es ON e.eventId = es.eventId
    WHERE e.driverId = d.driverId

    UNION ALL

    SELECT 'Season Score' AS event_name, ss.season_grade AS score, 2 as sortOrder
    FROM seasonsummary ss
    WHERE ss.driverId = d.driverId
) u
ORDER BY d.id, u.sortOrder

Note that if either the event or seasonsummary portion of the query yields zero results, the other results will still be included. The inner joins of your original query would have required both to be present to produce any results.

Results:

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

See this db<> fiddle for a demo.