sql server - Ignore the null values in aggregation -

sql server - Ignore the null values in aggregation -

i have table regions :

region typee cnt 1 new 1 1 leave 5 1 total 250 2 new 2 2 total 330 2 leave 10

and want create table this:

region new leave total 1 1 5 250 2 2 10 330

i tried this:

insert dp_cfcustcnt3 select region, case when typee = 'new' (select cnt typee = 'new') end new, case when typee = 'leave' (select cnt typee = 'leave') end leave, case when typee = 'total' (select cnt typee ='total') end total regions

but gives me table like:

region new leave total 1 1 null null 1 null 5 null 1 null null 250

thank advice.

you can either pivot or expanding on sql:

insert dp_cfcustcnt3 select region, sum(new) new, sum(leave) leave, sum(total) total ( select region, case typee when 'new' cnt else 0 end new, case typee when 'leave' cnt else 0 end leave, case typee when 'total' cnt else 0 end total test) info grouping part

sql-server