PIVOT INCLUDE NULLS

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

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 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:

---

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?

avec la fonction DECODE ?

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

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:

---

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

---

Merci