CODEWITHSUNDEEP
sqlsql-servert-sql
J.Snow
J.Snow

Reputation: 27

SQL Query with Pivot showing nulls even with Isnull

I'm using this query but it keeps showing me NULL values instead of 0 , i already tried many things and can't get it to return 0 on the null values.

select p.* from 
    (
    select  'Ordenado Base' as Custos, nome, sum(isnull(prre.ere,0)) as OBase 
    from pr
    inner join prre on pr.prstamp = prre.prstamp
    where year(pr.data) = 2017 and prre.cr = 1
    group by nome
    ) as Tabela1
    Pivot(
     sum(Tabela1.Obase)
    for nome in ([Alexandra Maria da Costa Migueis],[ALEXANDRE PEDRO GOMES FREITAS],[Amílcar Leonardo dos Santos],[Ana Filipa Leitão Costa Martins Claro Viana Machado],[ANA PATRICIA FRED FILIPE]) as P

Upvotes: 1

Views: 52

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

you need to change your query to include ISNULL in the select list. Assume that you do not have any Obase value for say [Alexandra Maria da Costa Migueis] then the value under it will be NULL

select 
        Custos,
        [Alexandra Maria da Costa Migueis]=ISNULL([Alexandra Maria da Costa Migueis],0),
        [ALEXANDRE PEDRO GOMES FREITAS]=ISNULL([ALEXANDRE PEDRO GOMES FREITAS],0),
        [Amílcar Leonardo dos Santos]=ISNULL([Amílcar Leonardo dos Santos],0),
        [Ana Filipa Leitão Costa Martins Claro Viana Machado]=ISNULL([Ana Filipa Leitão Costa Martins Claro Viana Machado],0),
        [ANA PATRICIA FRED FILIPE]=ISNULL([ANA PATRICIA FRED FILIPE],0)
from 
    (
        select  
            'Ordenado Base' as Custos, 
            nome, 
            sum(isnull(prre.ere,0)) as OBase 
        from pr
        inner join prre on pr.prstamp = prre.prstamp
            where year(pr.data) = 2017 and prre.cr = 1
        group by nome
    ) as Tabela1
    Pivot
    (
    sum(Tabela1.Obase)
    for nome in 
        (
        [Alexandra Maria da Costa Migueis],
        [ALEXANDRE PEDRO GOMES FREITAS],
        [Amílcar Leonardo dos Santos],
        [Ana Filipa Leitão Costa Martins Claro Viana Machado],
        [ANA PATRICIA FRED FILIPE]
        )-- was missing
    ) as P

Upvotes: 1

Related Questions