SQL fill in redundant values to table

I want to take the following table…

---

City	Company	Revenue
New York	A	400
New York	B	300
Singapore	B	400
Washington D.C.	C	400
London	D	400

and produce the below version.

---

City	Company	Present	Revenue
New York	A	1	400
New York	B	1	300
New York	C	0	0
New York	D	0	0
Singapore	A	0	0
Singapore	B	1	400
Singapore	C	0	0
Singapore	D	0	0
…			…
London	D	1	400

So, basically filling extra company values to encompass the entire dataset, adding a 1 in the “Present” field if they are present, otherwise zero…

I hope my question makes sense!

Answer

You can pull this off by first making a result set that has every combination of company and city. A cross join will do the trick here:

SELECT companies.company, cities.city 
FROM (SELECT DISTINCT Company FROM yourtable) companies,
  (SELECT DISTINCT City FROM yourtable) cities

We can now LEFT JOIN from that result set into your table to get to the final result set:

WITH all_values AS
(
  SELECT companies.company, cities.city 
  FROM (SELECT DISTINCT Company FROM yourtable) companies,
      (SELECT DISTINCT City FROM yourtable) cities
)
SELECT all_values.company, 
    all_values.city,
    CASE WHEN yourtable.company IS NOT NULL THEN 1 ELSE 0 END as Present,
    yourtable.Revenue
FROM 
    all_values
    LEFT OUTER JOIN yourtable 
        ON all_values.company = yourtable.company
        AND all_values.city = yourtable.city;