Sum each column in SQL and show them in Row

Question

I have a table 'task' like below

where ID is unique record and deptA.....deptD are departments. 1 denotes completed and 0 incomplete. I want to calculate sum of each department as below

I am trying to do like below but I don't know how to transpose to row

select Sum(deptA) as deptA, 
       Sum(deptB) as deptB, 
       Sum(deptC) as deptC, 
       Sum(deptD) as deptD, 
       count(*) as total 
  from task

Note - can't use pivot/unpivot function as my application doesn't support it.

Answer 1

You can unpivot with cross apply, and then aggregate:

select x.dept, sum(x.status) complete, sum(1 - x.status) incomplete
from task t
cross apply (values 
    ('deptA', deptA), ('deptB', deptB), ('deptC', deptC), ('deptD', deptD)
) x(dept, status)
group by x.dept

This assumes only 0/1 values in the departments columns, which simplifies the aggregation logic.