如何编写一个sql查询以获得来自1个表的以下结果？

Question

如何编写一个查询得到以下的输出：

主表/源表：

Answer 1

下面是一个使用union all和聚集了一个方法：

select weeknumber, sum(opened) as opened, sum(closed) as closed 
from ((select OpenWeekNumber as weeknumber, 1 as Opened, 0 as Closed 
     from maintable 
    ) union all 
     (select ClosedWeekNumber as weeknumber, 0 as Opened, 1 as Closed 
     from maintable 
     where ClosedWeekNumber > 0 
    ) 
    ) t 
group by weeknumber 
order by weeknumber;