1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| with cte_NOMENC (Code, CC, COMP, LIB_COMP, rn) as
(
select Code, CC, COMP, LIB_COMP
, row_number() over(partition by Code, CC order by COMP asc)
from NOMENC
)
select art.Code, art.CC, art.Lib1, art.Lib2
, max(case nom.rn when 1 then nom.COMP end) as COMP_1
, max(case nom.rn when 1 then lib.LIBEL_COMPL end) as LIB_1
, max(case nom.rn when 2 then nom.COMP end) as COMP_2
, max(case nom.rn when 2 then lib.LIBEL_COMPL end) as LIB_2
from ARTICLE as art
join cte_NOMENC as nom on nom.Code = art.Code
and nom.CC = art.CC
join LIBELLE as lib on lib.Code = nom.COMP
group by art.Code, art.CC, art.Lib1, art.Lib2; |
Partager