在不同值上使用自外連接擴展稀疏表

我有這個：

g1       | g2        | x
---------+-----------+-----
New York | Monday    | 210
New York | Tuesday   | 258
Chicago  | Monday    | 30
Chicago  | Wednesday | 25
LA       | Thursday  | 40

但我想要這個：

g1       | g2        | x
---------+-----------+-----
New York | Monday    | 210
New York | Tuesday   | 258
New York | Wednesday | NULL
New York | Thursday  | NULL
Chicago  | Monday    | 30
Chicago  | Tuesday   | NULL
Chicago  | Wednesday | 25
Chicago  | Thursday  | NULL
LA       | Monday    | NULL
LA       | Tuesday   | NULL
LA       | Wednesday | NULL
LA       | Thursday  | 40

換句話說，我想要記錄 和 的每個DISTINCT g1組合DISTINCT g2。我應該注意到g1和g2是任意分組（並不總是與位置或日期相關）。如何NULL根據叉積在表格中填寫這些值？

我不知道這是否更簡單，但我會更改語法以使用 ANSI JOIN 語法而不是(+)

select t1.g1,
 t1.g2,
 t2.x
from
(
 select distinct t1.g1, t2.g2
 from yourtable t1
 cross join (select g2 from yourtable) t2
) t1
left join yourtable t2
 on t1.g1 = t2.g1
 and t1.g2 = t2.g2
order by t1.g1, t1.g2

請參閱帶有展示的 SQL Fiddle

假設原始表是d，這似乎可行，但是有沒有更有效或更直接的方法？

SELECT cp.g1,
 cp.g2,
 d.x
FROM
 (SELECT DISTINCT d1.g1, d2.g2 FROM d d1, d d2
 ) cp,
 d
WHERE d.g1 (+) = cp.g1
AND d.g2 (+)   = cp.g2

引用自：https://dba.stackexchange.com/questions/37675