1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| SQL> with tab as ( select 1 NumCli ,'Jeanne' NomCli ,'ArtA' cmd from sys.dual union all
2 select 1 ,'Jeanne' ,'ArtA' from sys.dual union all
3 select 1 ,'Jeanne' ,'ArtB' from sys.dual union all
4 select 2 ,'Paul' ,'ArtA' from sys.dual union all
5 select 2 ,'Paul' ,'ArtC' from sys.dual union all
6 select 3 ,'Benoit' ,'ArtB' from sys.dual )
7 select numcli
8 , nomcli
9 , to_char(numcli)||trim(to_char( row_number() over (partition by numcli order by cmd), '09')) numcom
10 , cmd
11* from tab
SQL> /
NUMCLI NOMCLI NUMCOM CMD
---------- ------ ------- ----
1 Jeanne 101 ArtA
1 Jeanne 102 ArtA
1 Jeanne 103 ArtB
2 Paul 201 ArtA
2 Paul 202 ArtC
3 Benoit 301 ArtB |
Partager