SQL join to get the cartesian product of 2 columns out of 3 columns

Question

I have the following table:

create table #table (
  time int,
  key  char(1),
  val  int
)

with the following data:

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)

and I would like to come up with a join of that will yield the following rows/cols:

0  a  1
0  b  2
1  a  10
1  b  0
2  a  0
2  b  20

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.

I tried a few combinations of outer join but none of them seems to work.

Please help.

Thanks.

Answer 1

Try this:

SELECT DISTINCT t1.time, 
                t2.key, 
                IF(
                    t1.time = t2.time AND t1.key = t2.key AND t1.value = t2.value, 
                    t1.value, 
                    0
                ) AS value
FROM table_name t1
JOIN table_name t2
ORDER BY t1.time ASC,
         t2.key ASC;

Answer 2

Select times.time, keys.key, coalesce(table.val, 0) as val
From (Select distinct time from table) times
    cross join
    (Select distinct key from table) keys
    left outer join table
    on times.time = table.time and keys.key = table.key

Basically, your first construct the cross-product, then perform a left outer join.