CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Christopher Turnbull
Christopher Turnbull

Reputation: 1005

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!

Upvotes: 0

Views: 43

Answers (2)

eshirvana
eshirvana

Reputation: 24568

select city.city
     , company.company
     , coalesce(t.Revenue, 0) 
     , case when t.revenue is not null then 1 else 0 end as Present
from (select distinct company from tablename) company
cross join (select distinct City from tablename) city
left join tablename t
   on t.city = city.city
   and t.company = company.company

Upvotes: 0

JNevill
JNevill

Reputation: 50034

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;

Upvotes: 1

Related Questions