I have the following simplified table.
x
|---------------------|------------------|------------------|
| month | customer | value |
|---------------------|------------------|------------------|
| 1 | A | 20 |
|---------------------|------------------|------------------|
| 1 | B | 20 |
|---------------------|------------------|------------------|
| 1 | C | 20 |
|---------------------|------------------|------------------|
| 2 | A | 20 |
|---------------------|------------------|------------------|
| 2 | B | 20 |
|---------------------|------------------|------------------|
As you can see, There is not value for customer C, for month 2.
In this instance I need to programtically add month=’2′ customer=’C’ value=’0′, inside my select statement
SELECT month, customer, value FROM mytable
I have many more rows in my real table, but the end result needs to be the same customer list for each month.
Any help greatly appreciated.
Advertisement
Answer
Use a cross join
to generate the rows and a left join
to bring in the values:
select m.month, c.customer, coalesce(t.value, 0) as value
from (select distinct month from t) m cross join
(select distinct customer from t) c left join
t
on t.month = m.month and t.customer = c.customer;
Note: If you have other sources for the months or customers, then those are probably faster than using count(distinct)
.