Nulls in Dynamic Pivot - how to remove

• tdanley

  SSC-Addicted

  Points: 473

  More actions

  July 31, 2013 at 12:13 pm

  #281160

  Here is my code, i'm creating a dynamic pivot of sales for the last 6 days. Works great, except if I have a customer that does not have a sales on a particular day, I have nulls. I can't figure out how to take the nulls out of the pivot. I assume I have to use ISNULL but no where seems to work ... any suggestions or ideas on how to get rid of the pesky nulls?:crazy:

  SET @sql = N'SELECT *

  FROM (SELECT custid, sorderdate, qty

  FROM dbo.dailysales) AS D

  PIVOT(SUM(qty) FOR sorderdate IN(' + @cols + N')) AS P;';

  EXEC sp_executesql @sql;

  custid 6/19/2013 6/26/20137/3/2013

  customer1130138144

  customer2112017

  customer322NULL

  customer4423741

  customer5NULLNULL1

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 31, 2013 at 1:22 pm

  #1637544

  You need another list variable, like @cols, but built with the ISNULL in it.

  So, for example if @cols contains:

  [col1],[col2]

  Then @selectCols needs something like:

  ISNULL([col1],'') as [col1], ISNULL([col2],'') as [col2]

  and then use 'SELECT ' +@selectCols+' ...

  instead of 'SELECT *

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]