SQL查询填充0的缺失数据

Question

要填充堆叠和分组的 Highcharts ，我需要在我的数据中添加一些 0 值。我试过使用数字表，但无法弄清楚如何为每个日期缺少的每个员工添加 0。所以我有:

表格(数据、员工、编号、堆栈)

2012-10-13 00:00:00.000 Nick    3   corporate
2012-10-14 00:00:00.000 Jack    1   private
2012-10-15 00:00:00.000 Yannick 1   corporate
2012-10-15 00:00:00.000 Jack    2   corporate

我需要:

2012-10-13 00:00:00.000 Nick    3   corporate
2012-10-14 00:00:00.000 Nick    0   corporate
2012-10-15 00:00:00.000 Nick    0   corporate
2012-10-13 00:00:00.000 Jack    0   private
2012-10-14 00:00:00.000 Jack    1   private
2012-10-15 00:00:00.000 Jack    0   private
2012-10-13 00:00:00.000 Yannick 0   corporate
2012-10-14 00:00:00.000 Yannick 0   corporate
2012-10-15 00:00:00.000 Yannick 1   corporate
2012-10-13 00:00:00.000 Jack    0   corporate
2012-10-14 00:00:00.000 Jack    0   corporate
2012-10-15 00:00:00.000 Jack    2   corporate

我需要使用游标还是有更好的技巧？

Answer 1

    select dates.Datum, employees.Employee, isnull(tbl.Number,0), employees.Stack
      from (select distinct Datum from tbl) dates
cross join (select distinct Employee, Stack from tbl) employees
 left join tbl on tbl.Datum=dates.Datum and tbl.Employee = employees.Employee
                  and tbl.Stack = employees.Stack