title: How to fill in gaps in a SQL Table using a single statement as opposed to multiple steps
body: <p>Imagine I have the following tables:</p>
  
  <p><img src="https://i.stack.imgur.com/IokFe.png" alt="enter image description here"></p>
  
  <p>I want to fill in the gaps on table like so</p>
  
  <p><img src="https://i.stack.imgur.com/bLjZh.png" alt="enter image description here"></p>
  
  <p>Now I can do this using multiple steps with CTEs and temp tables building up groups, and max/min values and then inserting them.</p>
  
  <p>However I as wondering if there is a more elegant (!) way of doing this in single statement, that joins the <strong>source</strong> on itself with the <strong>months</strong>.</p>
  
  <p>Bonus points for doing this in ansi sql as opposed to tsql, but either is acceptable.</p>
  
  <p>===</p>
  
  <p>here is some sql to create a test</p>
  
  <pre><code>drop table months;
  create table months (year int, month int);
  
  insert into months(year, month) values
  (2000, 200007),   (2001, 200101),  (2002, 200201),
  (2000, 200008),   (2001, 200102),  (2002, 200202),
  (2000, 200009),   (2001, 200103),  (2002, 200203),
  (2000, 200010),   (2001, 200104),  (2002, 200204),
  (2000, 200011),   (2001, 200105),  (2002, 200205),
  (2000, 200012),   (2001, 200106),  (2002, 200206),
                    (2001, 200107),  (2002, 200007),
                    (2001, 200108),  (2002, 200208),
                    (2001, 200109),  (2002, 200208),
                    (2001, 200110),
                    (2001, 200111),
                    (2001, 200112)
  
  drop table source;
  create table source (name varchar(10), month int, item int, val float);
  
  insert into source(name, month, item, val) values
  ('bob', 200101, 1, 1.5),
  ('bob', 200102, 2, 1.5),
  ('bob', 200103, 2, 2),
  ('bob', 200108, 1, 0),
  ('bob', 200109, 10, 6.6),
  ('bob', 200110, 11, 2.2),
  ('bob', 200111, 9, 1),
  ('bob', 200207, 23, 0)
  </code></pre>