SQL Server – Applying GROUP BY with Multiple CASE WHEN

sql server

select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,CASE WHEN w_id = 1 THEN fee END as value1
,CASE WHEN w_id = 2 THEN fee END as value2
,CASE WHEN w_id not in ('1','2') THEN fee END as value3
from table1

The above code gives me a result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |    50
 1   |   0      | null   | null   |   150

but I'd like to group by e_id and get result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |   200

I've tried 

group by e_id

and 

group by e_id, CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END

but neither of these are grouping my data as I'd like. Is there a solution to this?

Best Answer

I think you just need to aggregate your value columns

create table table1(e_id int, w_id int, fee int)


insert into table1
values(1,1,100),(1,2,150),(1,3,50),(1,4,150),
(2,1,100),(2,1,150),(2,2,50),(2,4,150)


select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,SUM(CASE WHEN w_id = 1 THEN fee END) as value1
,SUM(CASE WHEN w_id = 2 THEN fee END) as value2
,SUM(CASE WHEN w_id not in ('1','2') THEN fee END) as value3
from table1
group by e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END
Related Question