how to remove null values from pivot sql

  • abdalah.mehdoini

    SSC Eights!

    Points: 873

    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.
  • Phil Parkin

    SSC Guru

    Points: 244578

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin

    SSC Guru

    Points: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • drew.allen

    SSC Guru

    Points: 76735

    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

  • Jeff Moden

    SSC Guru

    Points: 996645

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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