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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
|
select R.nom,R.code_reponsable ,
case when SERV1.niveau = 1 then SERV1.nom else
case when SERV2.Niveau = 1 then SERV2.nom else
case when SERV3.Niveau = 1 then SERV3.NOM ELSE
case when SERV4.Niveau = 1 then SERV4.nom else '' end
end
end
end as niv_1,
case when SERV1.niveau = 1 then SERV1.id_service else
case when SERV2.Niveau = 1 then SERV2.id_service else
case when SERV3.Niveau = 1 then SERV3.id_service ELSE
case when SERV4.Niveau = 1 then SERV4.id_service else '' end
end
end
end as id_niv_1,
case when SERV1.niveau = 2 then SERV1.nom else
case when SERV2.Niveau = 2 then SERV2.nom else
case when SERV3.Niveau = 2 then SERV3.NOM ELSE
case when SERV4.Niveau = 2 then SERV4.nom else '' end
end
end
end as niv_2,
case when SERV1.niveau = 2 then SERV1.id_service else
case when SERV2.Niveau = 2 then SERV2.id_service else
case when SERV3.Niveau = 2 then SERV3.id_service ELSE
case when SERV4.Niveau = 2 then SERV4.id_service else '' end
end
end
end as id_niv_2,
case when SERV1.niveau = 3 then SERV1.nom else
case when SERV2.Niveau = 3 then SERV2.nom else
case when SERV3.Niveau = 3 then SERV3.NOM ELSE
case when SERV4.Niveau = 3 then SERV4.nom else '' end
end
end
end as niv_3,
case when SERV1.niveau = 3 then SERV1.id_service else
case when SERV2.Niveau = 3 then SERV2.id_service else
case when SERV3.Niveau = 3 then SERV3.id_service ELSE
case when SERV4.Niveau = 3 then SERV4.id_service else '' end
end
end
end as id_niv_3,
case when SERV1.niveau = 4 then SERV1.nom else
case when SERV2.Niveau = 4 then SERV2.nom else
case when SERV3.Niveau = 4 then SERV3.NOM ELSE
case when SERV4.Niveau = 4 then SERV4.nom else '' end
end
end
end as niv_4,
case when SERV1.niveau = 4 then SERV1.id_service else
case when SERV2.Niveau = 4 then SERV2.id_service else
case when SERV3.Niveau = 4 then SERV3.id_service ELSE
case when SERV4.Niveau = 4 then SERV4.id_service else '' end
end
end
end as id_niv_4
from responsable R
outer apply ( select nom , id_service , niveau , id_service_parent from nomenclature_service NS where id_couleur = 15 and code_responsable =R.code_reponsable ) SERV1
outer apply ( select nom , id_service , niveau , id_service_parent from nomenclature_service NS where id_couleur = 15 and NS.id_service = SERV1.id_service_parent ) SERV2
outer apply ( select nom , id_service , niveau , id_service_parent from nomenclature_service NS where id_couleur = 15 and NS.id_service = SERV2.id_service_parent ) SERV3
outer apply ( select nom , id_service , niveau , id_service_parent from nomenclature_service NS where id_couleur = 15 and PF.id_service = SERV3.id_service_parent ) SERV4
order by R.nom , niv_1 ,niv_2,niv_3,niv_4 |
Partager