How to show missing rows with default values 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.
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.