SELECT 

sc.id_societe
,s.societe
,MAX(IF(cdf.fonction='maintien_plan_eau',scd.niveau,1)) as maintien_plan_eau
,MAX(IF(cdf.fonction='fonction_navigation',scd.niveau,1)) as fonction_navigation
,MAX(IF(cdf.fonction='securite_agents',scd.niveau,1)) as securite_agents
,MAX(IF(cdf.fonction='securite_usagers',scd.niveau,1)) as securite_usagers
,MAX(IF(cdf.fonction='securite_riverains',scd.niveau,1)) as securite_riverains
,MAX(IF(cdf.fonction='fonction_environ',scd.niveau,1)) as fonction_environ
,MAX(IF(cdf.fonction='protection_perennite',scd.niveau,1)) as protection_perennite
,MAX(IF(cdf.fonction='exploitation_surveillance',scd.niveau,1)) as exploitation_surveillance

FROM societe_composant  sc 
LEFT JOIN composant_desordre_fonction cdf ON sc.id_composant=cdf.id_composant
INNER JOIN societe s ON s.id_societe = sc.id_societe
INNER JOIN societe_composant_desordre scd ON scd.id_societe_composant=sc.id_societe_composant
GROUP BY sc.id_societe