mysql : how to group multiple row into one row

Question

help me pls.
my table in mysql look like this :

kd_mapel|Sem1   |Sem2   |Sem3
EKO-001 |79     |NULL   |NULL
EKO-002 |NULL   |80     |NULL
FIS-001 |77     |NULL   |NULL
FIS-002 |NULL   |76     |NULL
FIS-201 |NULL   |NULL   |81
GEO-001 |79     |NULL   |NULL
INA-001 |79     |NULL   |NULL
INA-002 |NULL   |80     |NULL
INA-003 |NULL   |NULL   |79
ING-001 |77     |NULL   |NULL
ING-002 |NULL   |79     |NULL
ING-003 |NULL   |NULL   |80

expected result is :

kd_mapel|Sem1   |Sem2   |Sem3
EKO     |79     |80     |NULL
FIS     |77     |76     |81
GEO     |79     |NULL   |NULL
INA     |79     |80     |79
ING     |77     |79     |80

is it possible to use the mysql code for these results? how?
thank you.

Answer 1

Try this query

select substring(kd_mapel, 1, locate("-", kd_mapel)-1) as kd, 
max(sem1), max(sem2), max(sem3)
from tbl
group by kd

FIDDLE

|  KD | MAX(SEM1) | MAX(SEM2) | MAX(SEM3) |
-------------------------------------------
| EKO |        79 |        80 |    (null) |
| FIS |        77 |        76 |        81 |
| GEO |        79 |    (null) |    (null) |
| INA |        79 |        80 |        79 |
| ING |        77 |        79 |        80 |

You can even use sum function if you have multiple sem record for kd_mapel.

Answer 2

Try

SELECT LEFT(kd_mapel, 3) kd_mapel, 
       MIN(Sem1) Semi1, 
       MIN(Sem2) Semi2, 
       MIN(Sem3) Semi3
  FROM table1
 GROUP BY LEFT(kd_mapel, 3)

Output:

| KD_MAPEL | SEMI1 |  SEMI2 |  SEMI3 |
--------------------------------------
|      EKO |    79 |     80 | (null) |
|      FIS |    77 |     76 |     81 |
|      GEO |    79 | (null) | (null) |
|      INA |    79 |     80 |     79 |
|      ING |    77 |     79 |     80 |

SQLFiddle