title: SQL join to get the cartesian product of 2 columns out of 3 columns
body: <p>I have the following table:</p>
  
  <pre><code>create table #table (
    time int,
    key  char(1),
    val  int
  )
  </code></pre>
  
  <p>with the following data:</p>
  
  <pre><code>insert into #table (time, key, val) values (0,"a",1)
  insert into #table (time, key, val) values (0,"b",2)
  insert into #table (time, key, val) values (1,"a",10)
  insert into #table (time, key, val) values (2,"b",20)
  </code></pre>
  
  <p>and I would like to come up with a join of that will yield the following rows/cols:</p>
  
  <pre><code>0  a  1
  0  b  2
  1  a  10
  1  b  0
  2  a  0
  2  b  20
  </code></pre>
  
  <p>Which is basically the cartesian product of the values of the first 2 columns, and their associated values in the 3rd column when the value exists, otherwise 0 when null.</p>
  
  <p>I tried a few combinations of outer join but none of them seems to work.</p>
  
  <p>Please help.</p>
  
  <p>Thanks.</p>