how to remove null values from pivot sql

• abdalah.mehdoini

  Ten Centuries

  Points: 1026

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  May 27, 2020 at 9:30 pm

  #3757869

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

  ---

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  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

  ---

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004615

  More actions

  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)