SQL - Insert rows with value=0 for each distinct element in Column A of each distinct element that it's missing in Column B

user_6790069 0
Viewed 50

I am kind of forced to use SQL and I am super new to it - so I need help on a particular case. I've been searching on the internet for similiar problems but couldn't find a solution yet.

My problem is that I have a dataset like this (column B ranges from 1 to 3):

:::Column A:::   :::Column B:::   :::Value:::
     'AB'              1              156
     'AB'              2              123
     'AB'              3             1235
     'CD'              2              155
     'EF'              3               55

What I need to achieve is:

:::Column A:::   :::Column B:::   :::Value:::
     'AB'              1              156
     'AB'              2              123
     'AB'              3             1235
     'CD'              1               0
     'CD'              2              155
     'CD'              3               0
     'EF'              1               0
     'EF'              2               0
     'EF'              3               55

I've been spending so much time on this problem, so I decided to ask here.

apache-spark-sql sql
2 Answers

You could left join your table on a query of the literals 1, 2 and 3, and use coalesce to generate the missing zeros:

SELECT    column_a, num, COALESCE(value, 0)
FROM      mytable
LEFT JOIN (SELECT 1 AS num 
           UNION ALL
           SELECT 2
           UNION ALL
           SELECT 3) t ON mytable.column_b = num
user_24227760

Use a cross join to generate the rows and a left join to bring in the values:

select a.column_a, b.column_b, coalesce(t.value, 0) as value
from (select distinct column_a from t) a cross join
     (select distinct column_b from t) b left join
     t
     on a.column_a = t.column_a and a.column_b = b.column_b;
user_11440350
Related