mysql - row for Gravity Forms columns

Question

Good afternoon, I’m pretty new to database and programming, but I like to move when necessary. I have a table in the database with responses from a form made in wordpress by Gravity Forms that presents the online responses in such a way that:

id  lead_id field_number    value
1   1       1               título1
2   1       2               descrição1
3   1       3               movimento1
4   1       4               status1
5   1       5               lat1
6   1       6               long1
7   1       7               nome1
8   1       8               email1
9   2       1               titulo2
10  2       2               descrição2
11  2       3               movimento2
12  2       4               status2
13  2       5               lat2
14  2       6               long2
13  2       7               nome2
14  2       8               email2

Since lead_id is the identifier of the form answers made by each user, field_number the identifier of each question in each form and value is the answer given to each question. I tried to turn the responses to the form into a new table, but when they were edited in wordpress the answers would not be updated. I want my final view inside the database in a new table that will be in columns instead of rows, this way:

lead_id titulo  descricao   movimento   status  lat     long    nome    email
1       titulo1 descricao1  movimento1  status1 lat1    long1   nome1   email1
2       titulo2 descricao2  movimento2  status2 lat2    long2   nome2   email2

I tried several answers here with similar topics, but I can not go far, always giving error. Remember that the new table needs to be dynamic, since the form table will grow as the answers. Could a good soul help? Thank you.

Answer 1

I was able to solve from this question asked on the platform in English.

https://stackoverflow.com/questions/19007699/mysql-rows-to-columns/37828455#37828455

in my case I adapted to the following code

SELECT lead_id, MAX(CASE WHEN field_number =1 THEN value END ) titulo, MAX(CASE WHEN field_number =2 THEN value END ) descricao, MAX(CASE WHEN field_number =3 THEN value END ) movimento, MAX(CASE WHEN field_number =4 THEN value END ) status, MAX(CASE WHEN field_number =5 THEN value END ) lat, MAX(CASE WHEN field_number =6 THEN value END ) long, MAX(CASE WHEN field_number =7 THEN value END ) nome, MAX(CASE WHEN field_number =8 THEN value END ) email FROM or1_rg_lead_detail GROUP BY lead_id