Bonjour,
la requête suivante ne recense pas les valeurs nulles (voir colonne COMPTE_NULL correspondant aux valeurs NULL de COL03)

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
WITH TABLE_1 AS (
          SELECT '01' AS COL01 ,'11' AS COL02,'8011551' AS COL03,'002' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'13' AS COL02,'8011553' AS COL03,'103' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'13' AS COL02,'8011553' AS COL03,'104' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,NULL AS COL03,'002' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,NULL AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,'7011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,'7011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,'7011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'13' AS COL02,'7011553' AS COL03,'103' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'13' AS COL02,'7011553' AS COL03,'104' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'11' AS COL02,'9011551' AS COL03,'002' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'11' AS COL02,'9011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'12' AS COL02,'9011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'12' AS COL02,'9011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'11' AS COL02,'9011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'15' AS COL02,'9011553' AS COL03,'103' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'15' AS COL02,'9011553' AS COL03,'104' AS COL04 FROM DUAL
 
)
SELECT 
*
FROM TABLE_1
PIVOT (
  COUNT(COL04) FOR COL03  IN ('8011551' AS COMPTE_8011551,'7011551' AS COMPTE_7011551,'9011551' AS COMPE_9011551,NULL AS COMPTE_NULL) 
)
 
COL01 COL02 COMPTE_8011551 COMPTE_7011551 COMPE_9011551 COMPTE_NULL
----- ----- -------------- -------------- ------------- -----------
02    11                 0              1             0           0 
03    12                 0              0             1           0 
01    13                 0              0             0           0 
01    11                 3              0             0           0 
02    13                 0              0             0           0 
03    11                 0              0             2           0 
03    15                 0              0             0           0 
 
 7 lignes sélectionnées
Afin de résoudre ce problème ,j'ai converti les valeurs nulles de COL03,de la manière suivante:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
WITH TABLE_1 AS (
          SELECT '01' AS COL01 ,'11' AS COL02,'8011551' AS COL03,'002' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'11' AS COL02,'8011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'13' AS COL02,'8011553' AS COL03,'103' AS COL04 FROM DUAL
UNION ALL SELECT '01' AS COL01 ,'13' AS COL02,'8011553' AS COL03,'104' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,NULL AS COL03,'002' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,NULL AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,'7011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,'7011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'11' AS COL02,'7011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'13' AS COL02,'7011553' AS COL03,'103' AS COL04 FROM DUAL
UNION ALL SELECT '02' AS COL01 ,'13' AS COL02,'7011553' AS COL03,'104' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'11' AS COL02,'9011551' AS COL03,'002' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'11' AS COL02,'9011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'12' AS COL02,'9011551' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'12' AS COL02,'9011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'11' AS COL02,'9011552' AS COL03,'100' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'15' AS COL02,'9011553' AS COL03,'103' AS COL04 FROM DUAL
UNION ALL SELECT '03' AS COL01 ,'15' AS COL02,'9011553' AS COL03,'104' AS COL04 FROM DUAL
)
,TABLE_2 AS (
SELECT 
COL01,COL02,NVL(COL03,'NULL') AS COL03,COL04
FROM TABLE_1
)
SELECT * FROM TABLE_2
PIVOT (
  COUNT(COL04) FOR COL03  IN ('8011551' AS COMPTE_8011551,'7011551' AS COMPTE_7011551,'9011551' AS COMPE_9011551,'NULL' AS COMPTE_NULL) 
)
 
COL01 COL02 COMPTE_8011551 COMPTE_7011551 COMPE_9011551 COMPTE_NULL
----- ----- -------------- -------------- ------------- -----------
02    11                 0              1             0           2 
03    12                 0              0             1           0 
01    13                 0              0             0           0 
01    11                 3              0             0           0 
02    13                 0              0             0           0 
03    11                 0              0             2           0 
03    15                 0              0             0           0 
 
 7 lignes sélectionnées

N'y avait-il pas une meilleure méthode?