0

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 :

project id | ID_EXPERT_1    | ID_EXPERT_2   |   ID_EXPERT_3 | ID_EXPERT_4 |
         1 |    12          |   42          |     null      |   null      |
         2 |    13          |   null        |     11        |   null      |
         3 |    34          |   22          |     null      |   null      |
         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 ?

BEFORE MARKED AS DUPLICATION:

I read about pivot on stackoverflow. 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 solve 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 ?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Altenrion
  • 764
  • 3
  • 14
  • 35
  • What is "project_id_1"? That is not in your original data. – Gordon Linoff Dec 21 '16 at 21:17
  • its ID_PROJECT = 1 – Altenrion Dec 21 '16 at 21:18
  • 2
    The accepted answer in the duplicate topic shows both static and dynamic pivoting in mysql. – Shadow Dec 21 '16 at 21:22
  • that is very close to my problem, but its rather hard to understand the logick of those prepared statements. I'll try my best – Altenrion Dec 21 '16 at 21:30
  • It is not simply close to your question, it is exactly the same question with the only possible answer within mysql. The logic of the prepared statements is very simple: dynamically produce as many case expressions as needed for each possible value used for pivoting. – Shadow Dec 21 '16 at 21:33
  • i'm reading the documentation about it wright now. – Altenrion Dec 21 '16 at 21:35
  • Btw you should consider doing the pivoting in your application, not in your mysql db. For dynamic pivoting an applucation level code may be more convenient and effective since this is about displaying the data differently. – Shadow Dec 21 '16 at 21:37
  • I understand this. The simpler sql queries are- more efficiently whole application works. But i mentioned : this is mostly for knowledge. To be able to write such queries inside one enviroment. – Altenrion Dec 21 '16 at 21:41
  • Don't post a new question that's exactly the same as the one that got closed. Edit the original question and request that it be reopened. – Barmar Dec 21 '16 at 21:52
  • Ok, i didn't know if it will be seen or not. But the effect of opening new one gave huge impact and help for resolving issue. I'm trying to implement those examples that were recommended, but now my database arguing about ```sql syntax``` in this part : ```) ) INTO @sql from marks_table; ``` – Altenrion Dec 21 '16 at 21:59
  • MySQL say ```check mysql version and doc how to use it near row ...``` with ```INTO @sql from marks_table;```. I have ```libmysql - mysqlnd 5.0.11-dev - 20120503``` – Altenrion Dec 21 '16 at 22:09
  • 2
    When it gets this complex, it is better to do it in application code. – Rick James Dec 22 '16 at 23:02
  • Agree; i did genetation of ```case``` statements for query in application logick. Firstly i've got experts ids for each block of my needs, than generated case queries, combined whole long query, and achieved needed result. Thanks to all. – Altenrion Dec 23 '16 at 08:19

0 Answers0