0

I have these two tables.

Student

id | name
 1    A
 2    B

Assignment

id | Student | Section
 1     1         1
 2     1         2
 3     1         3
 4     2         2
 5     3         3
 6     3         4

And I'm expecting to have the result pivot into dynamic number of columns.

Student | s1 | s2 | s3 
  A       1    2    3
  B       2
  C       3    4

Is it something feasible? I have used GROUP_CONCAT but that gives me a long concatenated string I'm thinking if it can be parsed into columns. The column names are not important I wonder how I should alias it. Any hints are appreciated, thanks a lot.

ydoow
  • 2,969
  • 4
  • 24
  • 40
  • What MySQL version? and can you please share your attempted query? – FanoFN Jul 09 '20 at 08:46
  • 1
    This is a two parter: first, make a rank per group (see [here](https://stackoverflow.com/q/532878), depending on your MySQL version maybe pick one of the later answers using window functions), then a [pivot](https://stackoverflow.com/q/12004603) using the rank as column. Are you sure you shall/want to do this in sql? You do not need to get that result in that format to display a table like this (in e.g. html) – Solarflare Jul 09 '20 at 09:25
  • @Solarflare thanks heaps. It's mysql5.3. The result will be later injected into Laravel. Alternatively I should be able to tweak the display in the row manipulation there. At this stage I'm trying to compare the performance between them. Would you think the sql way is slower? – ydoow Jul 09 '20 at 11:41
  • 1
    Yes, it is will probably be slower and less flexible (e.g. you cannot modify data this way, you don't know how many columns you get, ...). Also, generally speaking, sql is not the right place to do data formatting: the database stores your data, while the pivot is how you seem to want to display the data, which is the job of the application. It's probably actually just a small for loop in laravel, so you may want to ask a laravel question about how you would display that data the way you want it. And even if you need the pivot data in a model, you should probably still do it in laravel. – Solarflare Jul 09 '20 at 12:15
  • Thanks @Solarflare yup you're definitely right. The query can't do better in this case I've turned the handling to application. Cheers. – ydoow Jul 10 '20 at 04:15

1 Answers1

0

The following blog provides a Stored Procedure that will create the code, then execute it:

http://mysql.rjweb.org/doc.php/pivot

Rick James
  • 135,179
  • 13
  • 127
  • 222