Requête sur une table de relation 0,n - 0,n

Version imprimable

22/09/2011, 14h25
Jmdu_44fr

Requête sur une table de relation 0,n - 0,n

Bonjour à tous,

Je vous soumets un petit cas que je n'arrive pas à résoudre malgré mes multiples essais.

J'ai deux tables principales reliées par une table de relation.

La première table contient des statuts ou états
TS : Code - Libellé
ANI - Non Iimportée
AEC - En Cours
AVC - Validé et confirmé

La seconde table contient des enregistrements.
TR : No - ...
125 - ...
159 - ....

La table de relation porte la date où l'enregistrement de TR est entré dans l'état de TS
TR_D_TS : No - Code - Date
125 - AEC - 17/07/2011
159 - ANI - 15/08/2011
159 - AVC - 16/09/2011

Un enregistrement ne passe pas dans tous les états.

Voici ce que j'essaie d'obtenir via une requête SQL simple : La liste des dates d'un enregistrement pour tous les statuts de TS et Vide pour les états où l'enregistrement n'est pas passé :

No Code Date
125 - ANI -
125 - AEC - 17/07/2011
125 - AVC -
159 - ANI - 15/08/2011
159 - AEC -
159 - AVC - 16/09/2011

L'objectif final est de mettre ensuite les états de TS en colonne, les enregistrements en lignes de TR et à l'intersection la date de TR_D_TS ou vide.

No ANI AEC AVC
125 17/07/2011
159 15/08/2011 16/09/2011

Merci pour votre aide.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH TS AS (SELECT 'ANI' AS code, 'Non Importée' lib FROM dual
UNION ALL SELECT 'AEC' AS code, 'En cours' lib FROM dual
UNION ALL SELECT 'AVC' AS code, 'Validée' lib FROM dual),
TR AS (SELECT 125 NO FROM dual 
	UNION ALL SELECT 159 FROM dual),
tr_d_ts AS (SELECT 125 NO, 'AEC' code, '17/07/2011' dte FROM dual
UNION ALL SELECT 159 NO, 'ANI' code, '15/08/2011' dte FROM dual
UNION ALL SELECT 159 NO, 'AVC' code, '16/09/2011' dte FROM dual
)
SELECT tr.NO, ts.code, (SELECT dte FROM tr_d_ts WHERE tr_d_ts.NO = tr.NO AND tr_d_ts.code = ts.code) AS dte
FROM ts, tr
 
NO	CODE	DTE
125	ANI	
125	AEC	17/07/2011
125	AVC	
159	ANI	15/08/2011
159	AEC	
159	AVC	16/09/2011

Je pense que tu cherches à faire un pivot.

Code:

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
WITH TS AS (
SELECT 'ANI' AS Code, 'Non Importée'       AS LIB  FROM DUAL UNION ALL
SELECT 'AEC' AS Code, 'En Cours'           AS LIB  FROM DUAL UNION ALL
SELECT 'AVC' AS Code, 'Validé et confirmé' AS LIB  FROM DUAL 
),
TR AS (
SELECT 125 AS Num FROM DUAL UNION ALL
SELECT 159 AS Num FROM DUAL UNION ALL
SELECT 180 AS Num FROM DUAL UNION ALL
SELECT 201 AS Num FROM DUAL 
),
TR_D_TS AS (
SELECT 125 AS Num,'ANI' AS Code, SYSDATE    AS DT_HIST FROM DUAL UNION ALL 
SELECT 125 AS Num,'AEC' AS Code, SYSDATE +1 AS DT_HIST FROM DUAL UNION ALL 
SELECT 125 AS Num,'AVC' AS Code, SYSDATE +2 AS DT_HIST FROM DUAL UNION ALL 
SELECT 159 AS Num,'ANI' AS Code, SYSDATE    AS DT_HIST FROM DUAL UNION ALL 
SELECT 180 AS Num,'ANI' AS Code, SYSDATE    AS DT_HIST FROM DUAL UNION ALL  
SELECT 180 AS Num,'AVC' AS Code, SYSDATE +2 AS DT_HIST FROM DUAL UNION ALL 
SELECT 201 AS Num,'ANI' AS Code, SYSDATE    AS DT_HIST FROM DUAL UNION ALL 
SELECT 201 AS Num,'AEC' AS Code, SYSDATE +1 AS DT_HIST FROM DUAL 
)
SELECT TT2.NUM
  ,    MAX(TT2.ANI) AS ANI
  ,    MAX(TT2.AEC) AS AEC
  ,    MAX(TT2.AVC) AS AVC
FROM (
      SELECT TT.NUM,
             CASE WHEN CODE = 'ANI' THEN DT_HIST END ANI,
             CASE WHEN CODE = 'AEC' THEN DT_HIST END AEC,
             CASE WHEN CODE = 'AVC' THEN DT_HIST END AVC
      FROM (
            SELECT TR.NUM, 
                   TS.CODE, 
                   TR_D_TS.DT_HIST
            FROM TR CROSS JOIN TS
                 LEFT JOIN  TR_D_TS ON  TR.NUM  = TR_D_TS.NUM 
                                    AND TS.CODE = TR_D_TS.CODE
          ) TT
    ) TT2
GROUP BY   TT2.NUM

Avec un PIVOT :

Code:

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
with ts as
(
select 'ANI' as cod, 'Non importée' as lib from dual union all
select 'AEC'       , 'En cours'            from dual union all
select 'AVC'       , 'Validée'             from dual
)
  ,  tr as
(
select 125 as num from dual union all
select 159        from dual union all
select 170        from dual
)
  ,  trs as
(
select 125 as num, 'AEC' as cod, date '2011-07-17' as dte from dual union all
select 159       , 'ANI'       , date '2011-08-15'        from dual union all
select 159       , 'AVC'       , date '2011-09-16'        from dual
)
  select tr.num
       , max(case ts.cod when 'ANI' then trs.dte end) as dt_ani
       , max(case ts.cod when 'AEC' then trs.dte end) as dt_aec
       , max(case ts.cod when 'AVC' then trs.dte end) as dt_avc
    from ts cross join tr
         left outer join trs
           on trs.num = tr.num
          and trs.cod = ts.cod
group by tr.num
order by tr.num asc;
 
       NUM DT_ANI     DT_AEC     DT_AVC    
---------- ---------- ---------- ----------
       125            2011-07-17           
       159 2011-08-15            2011-09-16
       170

22/09/2011, 16h28
Jmdu_44fr

Un grand MERCI à vous,

Vos réponses m'ont permis de répondre à mon besoin. :D

Et quelle réactivité de votre part. :ccool:

Cordialement,
JeanMichel