1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| SELECT rr.intitule AS Nom_Site
,rr.idsite
,cr.libelle AS Groupe_site
,CASE WHEN r2013.cptcred LIKE '7%' THEN 'X'
ELSE NULL
END AS "2013"
,CASE WHEN r2014.cptcred LIKE '7%' THEN 'X'
ELSE NULL
END AS "2014"
,CASE WHEN r2015.cptcred LIKE '7%' THEN 'X'
ELSE NULL
END AS "2015"
,CASE WHEN r2016.cptcred LIKE '7%' THEN 'X'
ELSE NULL
END AS "2016"
,CASE WHEN r2017.cptcred LIKE '7%' THEN 'X'
ELSE NULL
END AS "2017"
,CASE WHEN r2013.cptcred LIKE '7%' THEN 'X'
WHEN r2014.cptcred LIKE '7%' THEN 'X'
WHEN r2015.cptcred LIKE '7%' THEN 'X'
WHEN r2016.cptcred LIKE '7%' THEN 'X'
WHEN r2017.cptcred LIKE '7%' THEN 'X'
ELSE NULL
END AS "RECETTES"
FROM l.rsite rr
LEFT JOIN l.asso_cat_site acr
ON rr.idsite = acr.idsite
LEFT JOIN l.cat_site cr
ON acr.id_cat_site = cr.id
LEFT JOIN l.r2013mou r2013
ON rr.idsite = r2013.idg_site AND r2013.cptcred LIKE '7%'
LEFT JOIN l.r2014mou r2014
ON rr.idsite = r2014.idg_site AND r2014.cptcred LIKE '7%'
LEFT JOIN l.r2015mou r2015
ON rr.idsite = r2015.idg_site AND r2015.cptcred LIKE '7%'
LEFT JOIN l.r2016mou r2016
ON rr.idsite = r2016.idg_site AND r2016.cptcred LIKE '7%'
LEFT JOIN l.r2017mou r2017
ON rr.idsite = r2017.idg_site AND r2017.cptcred LIKE '7%'
GROUP BY rr.intitule,rr.idsite, cr.libelle, r2013.cptcred, r2014.cptcred, r2015.cptcred, r2016.cptcred, r2017.cptcred
ORDER BY cr.libelle, rr.intitule |
Partager