0

I have the following table:

date      | name  | grade
-------------------------
2016-10-01| Alan  | 6
2016-10-03| Josh  | 8
2016-10-06| Alan  | 4
2016-10-05| Alan  | 5
2016-10-14| Rosie | 7

I need a query that returns a table that looks like this:

 name  | grade1 | grade2 | grade3
----------------------------------
 Alan  | 6      | 4      | 5
 Josh  | 8      | NULL   | NULL
 Rosie | 7      | NULL   | NULL

So it basically if it finds a name twice or more times, it adds a column and puts the grade there. Is such a thing even possible?

Also, the grades can be up to one trillion, so the solution linked here does not work. However it's almost impossible for a "name" to have more than 2 or 3 grades.

Community
  • 1
  • 1
Crates
  • 81
  • 1
  • 1
  • 8
  • 1
    Can someone please explain how my question is the same as the linked one? – Crates Nov 16 '16 at 14:12
  • If you truly can have a name with over 1 trillion grades then this is impossible in any RDBMS. You can't have that many columns in a result set. Outside of MySQL this would be done using a `pivot` or a `dynamic pivot` but with far fewer column possibilities. In mySQL your limited to max(case...) but that means writing 1 trillion case statements. which wouldn't be supported. So what your asking for can't be done UNLESS you're willing to limit the number of columns returned. – xQbert Nov 17 '16 at 19:01
  • If you're willing to accept fewer columns, you could use a user variable to assign a row number to each name ordered by the grade and then use a case statement to evaluate each row number putting the grade value in the column 1,2,3,4...etc) allowing you to show X grades, but not 1 trillion for a single name. – xQbert Nov 17 '16 at 19:05
  • Thanx! I managed to solve it in the end by using ROW_NUMBER() in the end. But thank you for your time anyway, much appreciated! – Crates Nov 18 '16 at 08:49
  • row_number() isn't a feature in mySQL... How did you use it? or is this not a mySQL database? – xQbert Nov 18 '16 at 14:03

0 Answers0