Post reply

how to remove null values from pivot sql

  • May 27, 2020 at 9:05 pm

    #3757865

    hello

    How can I remove NULL from SQL-Server PIVOT

    create  table #test
    (code_option varchar(10)
    ,agent char(2)
    ,exercice int 
    ,unite int
    )
     
    insert into #test values ('op100','a1','2019','5')
    insert into #test values ('op101','a2','2020','15')
    insert into #test values ('op102','a3','2018','10')
    insert into #test values ('op103','a2','2016','8')
    insert into #test values ('op104','a4','2017','5')
    insert into #test values ('op105','a1','2019','10')
    insert into #test values ('op107','a3','2019','5')
    insert into #test values ('op107','a4','2016','10')
    insert into #test values ('op108','a3','2015','20')
    insert into #test values ('op109','a2','2020','5')
    insert into #test values ('op110','a1','2018','6')
     

    SELECT distinct(agent),   
    [2020], [2019], [2018], [2017], [2016]  , [2015] 
    FROM  
    (SELECT  distinct(agent),exercice,unite
        FROM  #test ) AS SourceTable  
    PIVOT  
    (  
    SUm(unite)
    FOR  exercice IN ([2020], [2019], [2018], [2017], [2016]  , [2015] )  
    ) AS PivotTable;

     

    Attachments:
    You must be logged in to view attached files.

  • May 27, 2020 at 9:30 pm

    #3757869

    Remove? Or replace? What do you want to see instead?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 27, 2020 at 9:40 pm

    #3757876

    Here's an alternative way, which puts in zeros rather than NULLs

    SELECT t.agent
    ,[2020] = SUM((CASE WHEN t.exercice = 2020 THEN unite ELSE 0 END))
    ,[2019] = SUM((CASE WHEN t.exercice = 2019 THEN unite ELSE 0 END))
    ,[2018] = SUM((CASE WHEN t.exercice = 2018 THEN unite ELSE 0 END))
    ,[2017] = SUM((CASE WHEN t.exercice = 2017 THEN unite ELSE 0 END))
    ,[2016] = SUM((CASE WHEN t.exercice = 2016 THEN unite ELSE 0 END))
    ,[2015] = SUM((CASE WHEN t.exercice = 2015 THEN unite ELSE 0 END))
    FROM #test t
    GROUP BY t.agent

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 27, 2020 at 9:54 pm

    #3757877

    You can't remove them from the PIVOT, but you can use COALESCE() or ISNULL() to replace the NULL results with some other value.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • May 28, 2020 at 5:57 pm

    #3758259

    The method that Phil posted is known as a "CROSSTAB" and was the way we did pivots long before the  PIVOT operator became available.  CROSSTABs are also generally faster than PIVOTs and are certainly easier and faster than using multiple PIVOT operators to create (for example) parallel pivots in the results for things like QTY and AMT.

     

    --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 5 posts - 1 through 4 (of 4 total)

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