I have the following table called mdata
id M1 MOBJETO MDATOS
1 100 NOMBRE JUAN
2 100 APELLIDO PEREZ
3 100 CORREO [email protected]
4 200 NOMBRE JUAN
5 200 APELLIDO PEREZ
6 200 CORREO [email protected]
7 300 NOMBRE JUAN
8 300 APELLIDO PEREZ
9 300 CORREO [email protected]
I have another table called msection
M1
100
200
300
column m1 of table msection is primary key and foreign key of mdata
I have the following query
SELECT a.m1,
CASE B.mobjeto WHEN 'NOMBRE' THEN B.mdatos ELSE NULL END AS NOMBRE,
CASE B.mobjeto WHEN 'APELLIDO' THEN B.mdatos ELSE NULL END AS APELLIDO,
CASE B.mobjeto WHEN 'EMAIL' THEN B.mdatos ELSE NULL END AS EMAIL
FROM MSECCION AS A
LEFT JOIN MDATOS AS B ON A.M1 = B.M1
GROUP BY a.m1, B.mobjeto, B.mdatos
and it throws me the following
M1 NOMBRE APELLIDO EMAIL
100 JUAN NULL NULL
300 NULL NULL [email protected]
200 MARIA NULL NULL
100 NULL PEREZ NULL
300 PEDRO NULL NULL
300 NULL FLORES NULL
200 NULL TORRES NULL
100 NULL NULL [email protected]
200 NULL NULL [email protected]
How would I do or what do I need to stay like this?
M1 NOMBRE APELLIDO EMAIL
100 JUAN PEREZ [email protected]
200 MARIA TORRES [email protected]
300 PEDRO FLORES [email protected]
It looks like you just need to use an aggregation function and group by fewer columns: