Discussion :

[Casshern]

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?

[awalter1]

avec la fonction DECODE ?

DECODE(monchamp,null,'la valeur que tu veux signifier pour le null',monchamp)

[aieeeuuuuu]

Bonjour,

Normal, le NULL sont éliminés par des opérations d'agrégat.

Vous pouvez faire votre PIVOT "à l'ancienne" pour gérer vos NULL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 
		COL01,
		COL02,
		SUM(CASE WHEN COL03 = '8011551' THEN 1 ELSE 0 END) AS COMPTE_8011551,
		SUM(CASE WHEN COL03 = '7011551' THEN 1 ELSE 0 END) AS COMPTE_7011551,
		SUM(CASE WHEN COL03 = '9011551' THEN 1 ELSE 0 END) AS COMPTE_9011551,
		SUM(CASE WHEN COL03 IS NULL THEN 1 ELSE 0 END) AS COMPTE_NULL
FROM TABLE_1
GROUP BY 
		COL01,
		COL02

[Casshern]

Merci