How do you provide a default value for a null output column of a PIVOT operation

• WayneS

  SSC Guru

  Points: 95461

  More actions

  November 7, 2008 at 11:34 am

  #383330

  When using the PIVOT operation, if the column has no values then a NULL is in that column. Is there any way to have that column be some default value?

  You can see what I'm talking about with the following sql. I want all of the WeekXAmount columns to have a zero if there is a null.

  I've tried making a temp table (@) with default values, but the NULLs carry on through.

  Thanks,

  Wayne

  declare @FridayDate datetime

  set @FridayDate = '20081107'

  declare @FridayDates table (RowID integer identity, WeekStart datetime, WeekEnd datetime)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate, @FridayDate+6)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+7, @FridayDate+13)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+14, @FridayDate+20)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+21, @FridayDate+27)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+28, @FridayDate+34)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+35, @FridayDate+41)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+42, @FridayDate+48)

  insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+49, @FridayDate+55)

  DECLARE @Projections TABLE (

  budget_category varchar(3),

  amount numeric(10,2),

  DueDate datetime,

  Interval char(1),

  Period char(2))

  insert into @Projections

  select 110,139.50,'2008-11-21 00:00:00.000','D','21' UNION

  select 110,139.50,'2008-12-19 00:00:00.000','D','21' UNION

  select 111,27.50,'2008-11-07 00:00:00.000','D','07' UNION

  select 111,27.50,'2008-12-05 00:00:00.000','D','07' UNION

  select 182,489.81,'2008-11-14 00:00:00.000','D','15' UNION

  select 182,489.81,'2008-12-12 00:00:00.000','D','15' UNION

  select 200,75.00,'2008-11-21 00:00:00.000','D','27' UNION

  select 220,167.40,'2008-11-14 00:00:00.000','D','17' UNION

  select 220,167.40,'2008-12-12 00:00:00.000','D','17' UNION

  select 411,782.85,'2008-11-07 00:00:00.000','D','08' UNION

  select 411,782.85,'2008-12-05 00:00:00.000','D','08'

  select * from @Projections

  select

  pvt.budget_category,

  pvt.[1] [Week1Amount],

  pvt.[2] [Week2Amount],

  pvt.[3] [Week3Amount],

  pvt.[4] [Week4Amount],

  pvt.[5] [Week5Amount],

  pvt.[6] [Week6Amount],

  pvt.[7] [Week7Amount],

  pvt.[8] [Week8Amount]

  from

  (select

  p.budget_category,

  p.Amount,

  fd.RowID

  from @Projections p

  INNER JOIN @FridayDates fd ON p.DueDate = fd.WeekStart) AS p1

  PIVOT

  (

  sum([Amount])

  for [RowID] IN ([1],[2],[3],[4],[5],[6],[7],[8])

  ) AS pvt

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  November 7, 2008 at 11:40 am

  #895750

  Well - you can do it a few ways. The easiest is probably to simply use the isnull function on the columns AFTER the pivot operation (i.e. in the top-most query):

  ...

  select

  pvt.budget_category,

  isnull(pvt.[1],0) [Week1Amount],

  isnull(pvt.[2],0) [Week2Amount],

  isnull(pvt.[3],0) [Week3Amount],

  isnull(pvt.[4],0) [Week4Amount],

  isnull(pvt.[5],0) [Week5Amount],

  isnull(pvt.[6],0) [Week6Amount],

  isnull(pvt.[7],0) [Week7Amount],

  isnull(pvt.[8],0) [Week8Amount]

  from

  ....

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• WayneS

  SSC Guru

  Points: 95461

  More actions

  November 7, 2008 at 1:09 pm

  #895804

  Thanks Matt.

  (I hate the answers that are SOOO obvious... why didn't I think of it???:))

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  November 7, 2008 at 1:25 pm

  #895809

  WayneS (11/7/2008)

  ---

  Thanks Matt.

  (I hate the answers that are SOOO obvious... why didn't I think of it???:))

  Hey - it's a Friday....no biggie!!!!

  Speaking of which - time to replace the caffeine drip..:)

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?