I have this:

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

But I want this:

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

In other words, I want a record for every combination of DISTINCT g1 and DISTINCT g2. I should note that g1 and g2 are arbitrary groupings (not always location- or date-related). How do I fill in the table with these NULL values based on the cross product?

  • Do you really want that, or would you like g2 to just be every single date in a specific date range? Just asking for completeness. – Philᵀᴹ Mar 27 '13 at 23:27
  • Yes, I really want the cross product. I'm looking for a general approach where g1 and g2 are user-defined groupings (not always days of the week or dates). – quietmint Mar 28 '13 at 1:01

I don't know if this is any more straightforward, but I would change the syntax to use ANSI JOIN syntax instead of the (+)

select t1.g1,
  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

See SQL Fiddle with Demo


Assuming the original table is d, this seems to work, but is there a more efficient or straightforward way?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.