0

Let's say I have 2 tables in MySql/MariaDb: competisions and participants. There can be multiple participants in one competisions.

I want to

  • a) do an inner join

  • b) sort the participants by score

  • c) take top 5 participants

  • d) flatten the result into a single row

A flatten result would have pt1_name, pt1_score ... pt5_name, pt5_score columns

How to do it?

So far I've managed to do the a), b), c)

SELECT
      cm.id as 'cm_id',
      cm.title,
      cm.place,

      pt.id as 'pt_id',
      pt.full_name,
      pt.score

FROM competitions as cm
INNER JOIN participants as pt
ON pt.competition_id = cm.id


ORDER BY pt.score DESC
LIMIT 5

This will produce 5 rows. What I want is 1 row, such that the data from the 4 other rows would get quashed into the 1st row thus making it N columns longer.

NOTE: and If I query all the data from all competitions at once, it'd have to produce the amount of the flattened rows equal to the amount of rows in competitions.

How to do the d)?

Example of a result:

------------------------------------------------------------------------------------------------------
name    | title  | ple | p1    |score1| p2    |score2| p3    |score3| p4    |score4 | p5     |score5
------------------------------------------------------------------------------------------------------
compet1 | title1 | aaa | part1 | 3    | part2 | 4    | part3 | 4.2  | part4 | 30    | part5  | 32   
compet2 | title2 | fds | part1 | 1.33 | part2 | 2.34 | part3 | 2.8  | part4 | 120   | part5  | 150  
compet3 | title3 | ccc | part1 | 4.88 | part2 | 5.25 | part3 | 5.8  | part4 | 8.90  | part5  | 9.23 
------------------------------------------------------------------------------------------------------
8
  • This "flatten" is referred to as "pivot". MySQL does not implement it, use conditional aggregation.
    – Akina
    Commented Aug 5, 2022 at 9:29
  • @Akina how to use conditional aggregation in my case?
    – Kum
    Commented Aug 5, 2022 at 14:40
  • See the tag I added. There are many examples. And even more in stackoverflow.com with tag [pivot-table] Be sure to include [mysql] tag also.
    – Rick James
    Commented Aug 5, 2022 at 22:36
  • @RickJames why would you add a tag given that I'd already used the word "pivot" in my title?
    – Kum
    Commented Aug 5, 2022 at 22:39
  • Click on the tag to easily find other Questions about the same topic.
    – Rick James
    Commented Aug 5, 2022 at 22:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.