Mettre valeur 0 à la place de NULL avec Pivot

**MrCarrot** · 11/01/2017, 15h39

Bonjour,

J'ai un code avec plusieurs pivot qui renvoie un tableau.
Le problème étant que imaginons qu'il trouve une seule valeur, il va afficher seulement la ligne de cette valeur.
J'aimerai qu'il puisse afficher toutes les lignes avec des 0 à l'intérieur au lieu de les zappé...
Si quelqu'un sait comment faire et ou a une explication je suis preneur...

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
DECLARE @P_A_USER VARCHAR(10)
SET @P_A_USER = 'BBE' -- Ici on utilise le user BBE
 
set language us_english
select * from (
select datename(month, D1) as 'Mois', COALESCE(count(*), 0) as 'Total_nouveaux_contrats', 'Nouveaux contrats' as 'Donnee', 'a' as 'NumLigne' from CONTRAT
where C22 = @P_A_USER and year(D1) = year(getdate()) and C27 = 01
group by datename(month, D1)) as NvContrats
Pivot(
SUM(Total_nouveaux_contrats)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt1
UNION
select * from (
select datename(month, D1) as 'Mois', COALESCE(count(*), 0) as 'Total_contrats_renew_1an_perim', 'Renew 1 an avec modification périmètre et FI 30%' as 'Donnee', 'b' as 'NumLigne' from CONTRAT
where C22 = @P_A_USER and year(D1) = year(getdate()) and C27 = 02
group by datename(month, D1)) as NvContrats
Pivot(
SUM(Total_contrats_renew_1an_perim)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt2
UNION
select * from (
select datename(month, D1) as 'Mois', COALESCE(count(*), 0) as 'Total_contrats_renew_1an', 'Renew 1 an sans modification périmètre' as 'Donnee', 'c' as 'NumLigne' from CONTRAT
where C22 = @P_A_USER and year(D1) = year(getdate()) and C27 = 03
group by datename(month, D1)) as NvContrats
Pivot(
SUM(Total_contrats_renew_1an)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt3
UNION
select * from (
select datename(month, D1) as 'Mois', COALESCE(count(*), 0) as 'Total_contrats_renew_3ans', 'Renew 3 ans sans modification périmètre' as 'Donnee', 'd' as 'NumLigne' from CONTRAT
where C22 = @P_A_USER and year(D1) = year(getdate()) and C27 = 04
group by datename(month, D1)) as NvContrats
Pivot(
SUM(Total_contrats_renew_3ans)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt4
UNION
select * from (
select datename(month, DAT_ACT_PA) as 'Mois', COALESCE(count(*), 0) as 'RDV_realises', 'RDV réalisés' as 'Donnee', 'e' as 'NumLigne' from ACTION
where COD_COM = @P_A_USER and CO_T_CO_PA = 'RDVCL' and year(DAT_ACT_PA) = year(getdate())
group by datename(month, DAT_ACT_PA)) as RDVrealise
Pivot(
SUM(RDV_realises)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt5
UNION
select * from (
select datename(month, DAT_ACT_PR) as 'Mois', COALESCE(count(*), 0) as 'RDV_planifies', 'RDV planifiés' as 'Donnee', 'f' as 'NumLigne' from ACTION
where COD_COM = @P_A_USER and CO_T_CO_PA = 'RDVCL' and year(DAT_ACT_PR) = year(getdate()) and DAT_ACT_PA is NULL
group by datename(month, DAT_ACT_PR)) as RDVplanifie
Pivot(
SUM(RDV_planifies)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt6
UNION
select * from (
select datename(month, DATE_COMMANDE) as 'Mois', COALESCE(count(distinct SOCIETE.NO_SOCIETE), 0) as 'Nouveaux_comptes', 'Nouveaux comptes ouverts' as 'Donnee', 'g' as 'NumLigne' from SOCIETE
left join V_COMMANDE on V_COMMANDE.NO_SOCIETE = SOCIETE.NO_SOCIETE
where SOCIETE.COD_COM = @P_A_USER and year(DATE_COMMANDE) = year(getdate()) and (SOCIETE.CA = 0 and SOCIETE.CAN_1 = 0)
group by datename(month, DATE_COMMANDE)) as NvComptes
Pivot(
SUM(Nouveaux_comptes)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt7
UNION
select * from (
select datename(month, CREER_LE) as 'Mois', COALESCE(count(*), 0) as 'Nouvelles_affaires', 'Nouvelles affaires' as 'Donnee', 'h' as 'NumLigne' from AFFAIRE
where AFFAIRE.COD_COM = @P_A_USER and year(CREER_LE) = year(getdate())
group by datename(month, CREER_LE)) as NvAffaires
Pivot(
SUM(Nouvelles_affaires)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt8
UNION
select * from (
select datename(month, DATE_AFF) as 'Mois', COALESCE(count(*), 0) as 'Nouvelles_affaires_exploitation', 'Nouvelles affaires avec exploitation' as 'Donnee', 'i' as 'NumLigne' from AFFAIRE
where AFFAIRE.COD_COM = @P_A_USER and year(DATE_AFF) = year(getdate()) and N3 != 0
group by datename(month, DATE_AFF)) as NvAffairesExp
Pivot(
SUM(Nouvelles_affaires_exploitation)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt9
UNION
select * from (
select datename(month, DATE_AFF) as 'Mois', cast(COALESCE(SUM(N1), 0) as integer) as 'CA_nouvelles_affaires', 'CA nouvelles affaires (€)' as 'Donnee', 'j' as 'NumLigne' from AFFAIRE
where AFFAIRE.COD_COM = @P_A_USER and year(CREER_LE) = year(getdate())
group by datename(month, DATE_AFF)) as CaNvAffaires
Pivot(
SUM(CA_nouvelles_affaires)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt10
UNION
select * from (
select datename(month, DATE_AFF) as 'Mois', COALESCE(count(*), 0) as 'Nb_affaires_gagnees', 'Affaires gagnées' as 'Donnee', 'k' as 'NumLigne' from AFFAIRE
where AFFAIRE.COD_COM = @P_A_USER and year(DATE_AFF) = year(getdate()) and COD_ETAT = 02
group by datename(month, DATE_AFF)) as NbAffG
Pivot(
SUM(Nb_affaires_gagnees)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt11
UNION
select * from (
select datename(month, DATE_AFF) as 'Mois', COALESCE(count(*), 0) as 'Nb_affaires_perdues', 'Affaires perdues' as 'Donnee', 'l' as 'NumLigne' from AFFAIRE
where AFFAIRE.COD_COM = @P_A_USER and year(DATE_AFF) = year(getdate()) and COD_ETAT = 03
group by datename(month, DATE_AFF)) as NbAffP
Pivot(
SUM(Nb_affaires_perdues)
for Mois in (January, February, March, April, May, June, July, August, September, October, November, December)) as pvt12
 
order by NumLigne asc

Voici ce qu'il renvoie pour le user BBE http://imgur.com/a/Fdiwz
J'aimerai qu'il m'affiche toutes mes lignes et qu'il mette des 0 à la place des NULL ...
Merci pour vos aides !

**SQLpro** · 11/01/2017, 16h55

Utilisez COALESCE (fonction normative SQL)

A lire : http://sqlpro.developpez.com/cours/sqlaz/select/#L5.2

A +

**MrCarrot** · 12/01/2017, 09h54

Envoyé par SQLpro

Utilisez COALESCE (fonction normative SQL)

A lire : http://sqlpro.developpez.com/cours/sqlaz/select/#L5.2

A +

Si tu regardes bien j'ai déjà des COALESCE

**SQLpro** · 12/01/2017, 11h29

Votre requêtes sont imbitablement illisible !
Savez-vous ce qu'est l’indentation ?
Si vous êtes faignant sachez qu'il existe aiu moins 10 outils de formatage de lignes de code SQL en ligne !

A +

**MrCarrot** · 12/01/2017, 11h35

Envoyé par SQLpro

Votre requêtes sont imbitablement illisible !
Savez-vous ce qu'est l’indentation ?
Si vous êtes faignant sachez qu'il existe aiu moins 10 outils de formatage de lignes de code SQL en ligne !

A +

Je sais que c'est un peu sale à lire, malheureusement moi je reprend juste la requête de quelqu'un et on me demande de faire ça
Excusez moi pour le format
Voici le tout un peu mieux écrit :

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
DECLARE @P_A_USER VARCHAR(10)
SET @P_A_USER = 'BBE' --Ici on essaye avec le user BBE
 
SELECT *
FROM
  (SELECT datename(MONTH, D1) AS 'Mois',
          COALESCE(count(*), 0) AS 'Total_nouveaux_contrats',
          'Nouveaux contrats' AS 'Donnee',
          'a' AS 'NumLigne'
   FROM CONTRAT
   WHERE C22 = @P_A_USER
     AND year(D1) = year(getdate())
     AND C27 = 01
   GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_nouveaux_contrats)
                                                     FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt1
UNION
SELECT *
FROM
  (SELECT datename(MONTH, D1) AS 'Mois',
          COALESCE(count(*), 0) AS 'Total_contrats_renew_1an_perim',
          'Renew 1 an avec modification périmètre et FI 30%' AS 'Donnee',
          'b' AS 'NumLigne'
   FROM CONTRAT
   WHERE C22 = @P_A_USER
     AND year(D1) = year(getdate())
     AND C27 = 02
   GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_contrats_renew_1an_perim)
                                                     FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt2
UNION
SELECT *
FROM
  (SELECT datename(MONTH, D1) AS 'Mois',
          COALESCE(count(*), 0) AS 'Total_contrats_renew_1an',
          'Renew 1 an sans modification périmètre' AS 'Donnee',
          'c' AS 'NumLigne'
   FROM CONTRAT
   WHERE C22 = @P_A_USER
     AND year(D1) = year(getdate())
     AND C27 = 03
   GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_contrats_renew_1an)
                                                     FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt3
UNION
SELECT *
FROM
  (SELECT datename(MONTH, D1) AS 'Mois',
          COALESCE(count(*), 0) AS 'Total_contrats_renew_3ans',
          'Renew 3 ans sans modification périmètre' AS 'Donnee',
          'd' AS 'NumLigne'
   FROM CONTRAT
   WHERE C22 = @P_A_USER
     AND year(D1) = year(getdate())
     AND C27 = 04
   GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_contrats_renew_3ans)
                                                     FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt4
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DAT_ACT_PA) AS 'Mois',
          COALESCE(count(*), 0) AS 'RDV_realises',
          'RDV réalisés' AS 'Donnee',
          'e' AS 'NumLigne'
   FROM ACTION
   WHERE COD_COM = @P_A_USER
     AND CO_T_CO_PA = 'RDVCL'
     AND year(DAT_ACT_PA) = year(getdate())
   GROUP BY datename(MONTH, DAT_ACT_PA)) AS RDVrealise Pivot(SUM(RDV_realises)
                                                             FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt5
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DAT_ACT_PR) AS 'Mois',
          COALESCE(count(*), 0) AS 'RDV_planifies',
          'RDV planifiés' AS 'Donnee',
          'f' AS 'NumLigne'
   FROM ACTION
   WHERE COD_COM = @P_A_USER
     AND CO_T_CO_PA = 'RDVCL'
     AND year(DAT_ACT_PR) = year(getdate())
     AND DAT_ACT_PA IS NULL
   GROUP BY datename(MONTH, DAT_ACT_PR)) AS RDVplanifie Pivot(SUM(RDV_planifies)
                                                              FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt6
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DATE_COMMANDE) AS 'Mois',
          COALESCE(count(DISTINCT SOCIETE.NO_SOCIETE), 0) AS 'Nouveaux_comptes',
          'Nouveaux comptes ouverts' AS 'Donnee',
          'g' AS 'NumLigne'
   FROM SOCIETE
   LEFT JOIN V_COMMANDE ON V_COMMANDE.NO_SOCIETE = SOCIETE.NO_SOCIETE
   WHERE SOCIETE.COD_COM = @P_A_USER
     AND year(DATE_COMMANDE) = year(getdate())
     AND (SOCIETE.CA = 0
          AND SOCIETE.CAN_1 = 0)
   GROUP BY datename(MONTH, DATE_COMMANDE)) AS NvComptes Pivot(SUM(Nouveaux_comptes)
                                                               FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt7
UNION
SELECT *
FROM
  (SELECT datename(MONTH, CREER_LE) AS 'Mois',
          COALESCE(count(*), 0) AS 'Nouvelles_affaires',
          'Nouvelles affaires' AS 'Donnee',
          'h' AS 'NumLigne'
   FROM AFFAIRE
   WHERE AFFAIRE.COD_COM = @P_A_USER
     AND year(CREER_LE) = year(getdate())
   GROUP BY datename(MONTH, CREER_LE)) AS NvAffaires Pivot(SUM(Nouvelles_affaires)
                                                           FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt8
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
          COALESCE(count(*), 0) AS 'Nouvelles_affaires_exploitation',
          'Nouvelles affaires avec exploitation' AS 'Donnee',
          'i' AS 'NumLigne'
   FROM AFFAIRE
   WHERE AFFAIRE.COD_COM = @P_A_USER
     AND year(DATE_AFF) = year(getdate())
     AND N3 != 0
   GROUP BY datename(MONTH, DATE_AFF)) AS NvAffairesExp Pivot(SUM(Nouvelles_affaires_exploitation)
                                                              FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt9
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
          cast(COALESCE(SUM(N1), 0) AS integer) AS 'CA_nouvelles_affaires',
          'CA nouvelles affaires (€)' AS 'Donnee',
          'j' AS 'NumLigne'
   FROM AFFAIRE
   WHERE AFFAIRE.COD_COM = @P_A_USER
     AND year(CREER_LE) = year(getdate())
   GROUP BY datename(MONTH, DATE_AFF)) AS CaNvAffaires Pivot(SUM(CA_nouvelles_affaires)
                                                             FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt10
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
          COALESCE(count(*), 0) AS 'Nb_affaires_gagnees',
          'Affaires gagnées' AS 'Donnee',
          'k' AS 'NumLigne'
   FROM AFFAIRE
   WHERE AFFAIRE.COD_COM = @P_A_USER
     AND year(DATE_AFF) = year(getdate())
     AND COD_ETAT = 02
   GROUP BY datename(MONTH, DATE_AFF)) AS NbAffG Pivot(SUM(Nb_affaires_gagnees)
                                                       FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt11
UNION
SELECT *
FROM
  (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
          COALESCE(count(*), 0) AS 'Nb_affaires_perdues',
          'Affaires perdues' AS 'Donnee',
          'l' AS 'NumLigne'
   FROM AFFAIRE
   WHERE AFFAIRE.COD_COM = @P_A_USER
     AND year(DATE_AFF) = year(getdate())
     AND COD_ETAT = 03
   GROUP BY datename(MONTH, DATE_AFF)) AS NbAffP Pivot(SUM(Nb_affaires_perdues)
                                                       FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt12
ORDER BY NumLigne ASC

**MrCarrot** · 12/01/2017, 11h53

Pour éviter de lire tout le message précédent autant regarder pour un seul select pourquoi ça ne fonctionne pas car ils sont tous pareil :

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
 
SELECT *
FROM
  (SELECT datename(MONTH, D1) AS 'Mois',
          COALESCE(count(*), 0) AS 'Total_nouveaux_contrats',
          'Nouveaux contrats' AS 'Donnee',
          'a' AS 'NumLigne'
   FROM CONTRAT
   WHERE C22 = @P_A_USER
     AND year(D1) = year(getdate())
     AND C27 = 01
   GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_nouveaux_contrats)
                                                     FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt1
UNION
SELECT * (...)

Mettre valeur 0 à la place de NULL avec Pivot

Langage SQL

Discussions similaires

Partager

Partager