Bring results equal to 0 as blank value in a query in SQL Server

Question

I have a query below that the records that have nothing is coming with 0. I wish it could come blank. Example:

select 
F.C37 [Pontuação],
ISNULL(count(distinct TD.UsuIDCliente),'-')   [Qtde_Clientes Detratores],
ISNULL(count(distinct TN.UsuIDCliente),'-')    [Qtde_Clientes Neutro],
ISNULL(count(distinct TP.UsuIDCliente),'-')    [Qtde_Clientes Promotores]
from FRM_8 F
left join Tarefa TD on TD.TarID = F.TarefaID and F.C37 between 0 and 5 AND TD.TarFechamento between '01-07-2017' and '17-08-2017' -- Detratores
left join Tarefa TN on TN.TarID = F.TarefaID and F.C37 between 6 and 8 AND TN.TarFechamento between '01-07-2017' and '17-08-2017' --Neutro
left join Tarefa TP on TP.TarID = F.TarefaID and F.C37 between 9 and 10 AND TP.TarFechamento between '01-07-2017' and '17-08-2017' --Promotores
where 
F.C38 is not null and F.C37 is not null
group by F.C37
order by  
CASE F.C37 WHEN '1' THEN 0 
                    WHEN '2' THEN 1
                    WHEN '3' THEN 2
                    WHEN '4' THEN 3
                    WHEN '5' THEN 4
                    WHEN '6' THEN 5
                    WHEN '7' THEN 6
                    WHEN '8' THEN 7
                    WHEN '9' THEN 8
                    WHEN '10' THEN 9
END

Today it’s coming that way:

Pontuacao      Clientes Detratores    Clientes Neutros
4                    0                      2
5                    1                      5
6                    3                      0

I want instead of coming 0, come NULL or blank.

Answer 1

The problem is that the expected return type in the column is int.

You can return null or convert column to varchar. Follow example code:

select
c.id,
count(distinct t1.id) as chave1,
count(distinct t2.id) as chave2,
(case count(distinct t3.id) when 0 then null else count(distinct t3.id) end)  as chave3,
(case count(distinct t4.id) when 0 then '---' else cast(count(distinct t4.id) as varchar) end)  as chave4
from chaves c
left outer join tabela t1 on t1.chave = c.id and t1.chave = 1
left outer join tabela t2 on t2.chave = c.id and t2.chave = 2
left outer join tabela t3 on t3.chave = c.id and t3.chave = 3
left outer join tabela t4 on t4.chave = c.id and t4.chave = 4
group by c.id

column 3, exemplifies the return of null,

column 4, the return converted to varchar.

I put in SQL Fiddle: http://sqlfiddle.com/#! 6/c2cb0/12

Answer 2

I don’t know if you’ll find gambiarra but you can check if the return is 0 and return empty:

select 
F.C37 [Pontuação],
CASE WHEN ISNULL(count(distinct TD.UsuIDCliente),'-') = 0 THEN '' ELSE CONVERT(VARCHAR(15),ISNULL(count(distinct TD.UsuIDCliente),'-')) [Qtde_Clientes Detratores],
CASE WHEN ISNULL(count(distinct TN.UsuIDCliente),'-') = 0 THEN '' ELSE CONVERT(VARCHAR(15),ISNULL(count(distinct TN.UsuIDCliente),'-')) [Qtde_Clientes Neutro],
CASE WHEN ISNULL(count(distinct TP.UsuIDCliente),'-') = 0 THEN '' ELSE CONVERT(VARCHAR(15),ISNULL(count(distinct TP.UsuIDCliente),'-')) [Qtde_Clientes Promotores]