Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  FreshPhilOfSO 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). –  user113215 Mar 28 '13 at 1:01
add comment

2 Answers

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,
  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

See SQL Fiddle with Demo

share|improve this answer
add comment

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

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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