I have MySQL database. There is a log table with project marks. I need to find out, what marks have each project and what expert have or have not voted for this project. So, the structure of the table is this:
| ID_MARK | ID_EXPERT | ID_PROJECT | mark_1 | mark_2 | ... | mark_n | TOTAL_MARK |
---------------------------------------------------------------------------------
| 1 | 1 | 1 | x | x | ... | x | 12 |
| 2 | 1 | 2 | x | x | ... | x | 13 |
| 3 | 2 | 1 | x | x | ... | x | 42 |
| 4 | 1 | 3 | x | x | ... | x | 34 |
| 5 | 2 | 3 | x | x | ... | x | 22 |
| 6 | 3 | 4 | x | x | ... | x | 55 |
| 7 | 3 | 2 | x | x | ... | x | 11 |
And the structure that i want to obtain with some query is that :
| ID_EXPERT_1 | ID_EXPERT_2 | ID_EXPERT_3 | ID_EXPERT_4 |
project_id_1 | 12 | 42 | null | null |
project_id_2 | 13 | null | 11 | null |
project_id_3 | 34 | 22 | null | null |
project_id_4 | null | null | 55 | null |
I know how to crete this all outside of database with small requests, but I want to upgrade my knowledge to be able to create such a hard queries. That query might be not very efficient or not very fast, but it is not necessary here.
I read about virtual tables but not sure if it is possible to implement them here working with mysql?
What can be used to create such a query result , working with MySQL ?
EDITION AFTER MARKED AS DUPLICATION:
I read about pivot. My question is deeper.
In other examples on stackoverflow people shown the query practisies where they knew about the amount of potential columns, and wrote exact same case statements. My question is exactly about how to make query, that would solove this issue. That would create a query like i would do in programming language like :
$experts = get_experts_ids_from_db();
foreach($experts as $expert){
...
}
I know that it is possible to make @variables and virtual tables in MsSQL inside of a long query text, to store temp data. But is t possible in mysql ?
