Post reply

Removing null from Result set

  • August 25, 2015 at 9:53 am

    #307041

    Hi all,

    Please help me with this code, I want to remove the nulls from the result set so the result is 1 line. Code and results are below:

    SELECT

    CustomerNumber,

    (case when yearseq = 2012 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2012',

    (case when yearseq = 2013 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2013',

    (case when yearseq = 2014 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2014',

    (case when yearseq = 2015 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2015'

    FROM ProductPower

    Where customernumber in ('1107937') and yearseq is not null

    group by CustomerNumber,yearseq

    order by customernumber

    The results look like this currently:

    What can I do to my code to remove the Nulls to the entire result is just 1 line?

    Thanks so much

    -Self taught SQL Noob

  • August 25, 2015 at 10:44 am

    #1822273

    [SOLVED]

    SELECT

    CustomerNumber,

    SUM(CASE WHEN yearseq = 2012 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0) ELSE 0 END ) AS [2012],

    SUM(CASE WHEN yearseq = 2013 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0) ELSE 0 END ) AS [2013],

    SUM(CASE WHEN yearseq = 2014 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0) ELSE 0 END ) AS [2014],

    SUM(CASE WHEN yearseq = 2015 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0) ELSE 0 END ) AS [2015]

    FROM ProductPower

    Where customernumber in ('1107937') and yearseq is not null

    group by CustomerNumber

    order by customernumber

  • August 25, 2015 at 5:01 pm

    #1822352

    To see more about how the CROSSTAB code above works, please see the following article...

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    To see how to do it without having to know the number of years, please see the following article...

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply