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
    Aug 5 at 9:29
  • @Akina how to use conditional aggregation in my case?
    – Kum
    Aug 5 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
    Aug 5 at 22:36
  • @RickJames why would you add a tag given that I'd already used the word "pivot" in my title?
    – Kum
    Aug 5 at 22:39
  • Click on the tag to easily find other Questions about the same topic.
    – Rick James
    Aug 5 at 22:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.