How to show missing rows with default values

Question

I have a table Category which has a few missing records for a few months. The table looks like this

Category Month Amount
Opt January 12.00
Opt February 0.00
Opt March -1042000.00
Opt April 0.00
Opt May 0.00
Opt June 0.00
Opt July -782663.00
Opt November 0.00
Opt December 0.00

This table has missing rows for a few months. I would like to query and show this data as well.

So I created a month table

MonthNameLong MonthNameShort
January Jan
February Feb
March Mar
April Apr
May May
June Jun
July Jul
August Aug
September Sep
October Oct
November Nov
December Dec

So that I can do a right join on this and get the missing details as well. The query I wrote is

select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
from Category s
RIGHT JOIN Month m 
ON s.Month = m.MonthNameLong

And the result I get is

Category MonthNameLong Amount
Opt January 12.00
Opt February 0.00
Opt March -1042000.00
Opt April 0.00
Opt May 0.00
Opt June 0.00
Opt July -782663.00
NULL August 0.00
NULL September 0.00
NULL October 0.00
Opt November 0.00
Opt December 0.00

But instead of NULL category is there a way I can get Opt as the category.

I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0

This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.

Answer 1

I have updated your fiddle with:

• add one more category for to see the query is correct when there is more than 1 category
• alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.

Edited fiddle.

The final query is

With 
Categories AS (
 SELECT DISTINCT Category
 FROM Category
 ),
MonthCategories AS (
 SELECT m.MonthNameLong, cat.Category
 FROM Month m, Categories cat
 )
select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
FROM MonthCategories mc
LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
 AND cat.Category = mc.Category;

Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.

PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.