tsql – pivot sum of employee

I have here a sample table data which i want to use pivot and make my DedCode data into column. the AMount example is already computed to its total but how can i count the number of employee if it is same with branch,deptcode and emptype.

my sample table data.

expected output:

Answer

You can use conditional aggregation:

select branch, deptcode, emptype, sum(empcount) as empcount,
       sum(case when dedcode = 'PHIC' then amount else 0 end) as phic,
       sum(case when dedcode = 'SLOAN' then amount else 0 end) as sloan,
       sum(case when dedcode = 'VLOAN' then amount else 0 end) as vloan
from t
group by branch, deptcode, emptype;