Jerome Provensal Jerome Provensal - 13 days ago 9
SQL Question

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

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

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;