0

I have a pivot table showing the total of orders in each month of year. Each column is a year and each row is a month. Some months don't have values at all, e.g Sept 2006. There is still a 2006 column for other months that have values and there is still a Sept row for the other years. Just the cell for Sept 2006 is empty.

How can I have a 0 put in this cell?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
A Jackson
  • 2,776
  • 7
  • 34
  • 45

3 Answers3

1

Pivot table in what? Excel? can't you just do that in Excel? well if not...

Just because the columns have a 2006 and the rows have a Sept, doesn't mean your data source has a row for Sept 2006. If it does then Lynette has your answer. If it doesn't you'll need to outer join to a table/sql of all months and years to create your null records. And then you can coalesce those nulls to zero.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
  • 1
    I'm using the Pivot function in SQL for Oracle. There is no data for Sept 2006. I guess I'll have to do what you said and join to a table of months. – A Jackson Nov 16 '10 at 21:28
  • So can you accept the answer as correct if that fixes your issue? Of course the table can be real or virtual. I suggest just building a date table... tremendously useful in many situations. – Stephanie Page Nov 17 '10 at 15:22
1

Use nvl(value, 0) in your query. It substitutes NULL (empty) with 0.

Martin Schapendonk
  • 12,893
  • 3
  • 19
  • 24
0

If by "empty" you mean NULL, try using Coalesce( UrMonth, 0 )

Lynette Duffy
  • 404
  • 2
  • 5