Mysql Row To Column Select Specific Data

Question

I have column user and rating.

SELECT rating.idUser, user.nmUser, rating.idBengkel, rating.nilai FROM `rating`
JOIN user on rating.idUser = user.idUser
WHERE rating.idBengkel=1 or rating.idBengkel=2

Result :

+--------+---------------------------+-----------+-------+
| idUser |          nmUser           | idBengkel | nilai |
+--------+---------------------------+-----------+-------+
|     10 | Hudson mas77              |         1 |     5 |  
|     11 | Vina Nurfadzilah          |         1 |     5 |  
|     12 | Angelica Amartya          |         1 |     5 |  
|     15 | Syahrul K                 |         1 |     4 |  
|     27 | Ashar Murdihastomo        |         1 |     5 |  
|     28 | Eril Obeit Choiri         |         1 |     2 |  
|     29 | Ariyadi                   |         1 |     3 |  
|     30 | Robertus Dwian Augusta    |         1 |     4 |  
|     31 | Irfan Setiaji             |         1 |     4 |  
|     33 | Baby Ayuna                |         1 |     5 |  
|      9 | Nur k hamid               |         2 |     5 |  
|     10 | Hudson mas77              |         2 |     5 |  
|     13 | Yuana Putra               |         2 |     4 |  
|     14 | Nanda Aulia Irza Ramadhan |         2 |     4 |  
|     26 | taufiq rahman             |         2 |     5 |  
|     27 | Ashar Murdihastomo        |         2 |     5 |  
|     28 | Eril Obeit Choiri         |         2 |     5 |  
|     30 | Robertus Dwian Augusta    |         2 |     4 |  
|     44 | halim budiono             |         2 |     1 |  
+--------+---------------------------+-----------+-------+

When i try to get similar records using this query

SELECT rating.idUser, user.nmUser FROM rating
JOIN user 
ON rating.idUser = user.idUser
WHERE rating.idBengkel = 1 and rating.idUser 
IN (SELECT rating.idUser from rating WHERE rating.idBengkel = 2) 
ORDER by idUser

Result :

+-----------+------------------------+
| idUser    |         nmUser         |  
+-----------+------------------------+
|        10 | Hudson mas77           |  
|        27 | Ashar Murdihastomo     | 
|        28 | Eril Obeit Choiri      | 
|        30 | Robertus Dwian Augusta |  
+-----------+------------------------+

The result work fine, but I want show column 'nilai' as ItemX and ItemY. Those are user similar data. In this case I have 4 similar user who rate on idBengkel=1 and idBengkel=2 as the results above. I want it like the table below.

+--------+------------------------+-------+-------+
| idUser |         nmUser         | ItemX | ItemY | 
+--------+------------------------+-------+-------+
|     10 | Hudson mas77           |     5 |     5 |  
|     27 | Ashar Murdihastomo     |     5 |     5 |  
|     28 | Eril Obeit Choiri      |     2 |     5 |  
|     30 | Robertus Dwian Augusta |     4 |     4 |  
+--------+------------------------+-------+-------+

I need solution for this and i was trying with this solution in https://stackoverflow.com/a/7976379/12396302 but it resulting more than one row. Please help me, I cant implement that query's solution. Regards!

Answer 1

I think you need below query -

SELECT rating.idUser,
       user.nmUser,
       MAX(CASE WHEN rating.idBengkel = 1 THEN rating.nilai END) ItemX,
       MAX(CASE WHEN rating.idBengkel = 2 THEN rating.nilai END) ItemY,
  FROM `rating`
  JOIN user on rating.idUser = user.idUser
 WHERE rating.idBengkel IN (1, 2)
 GROUP BY rating.idUser,
          user.nmUser