Case statement without null value

Question

I am trying to compare YOY data and I am trying to use a case statement in order to split by year. What I have been trying to do is that

(Case
When year(date) = 2010 Then 'sales year 2010'
When year(date) = 2011 Then 'sales year 2011'
Else 
End) as Year 

but the above one isn't working cause I wanted to get each year data by column, not as rows under the same column.

So, next what I tried is that

(Case
When year(date) = 2010 Then sum(sales) End) as 'sales year 2010', 
(Case
When year(date) = 2011 Then sum(sales) End) as 'sales year 2011' 

And I got the below.

Item	Sales Year 2010	Sales Year 2011
A	null	2,500
A	5,000	null
B	null	200
B	7,000	null
C	null	2,500
C	5,000	null
D	null	200
D	7,000	null

What I want to get is this.

Item	Sales Year 2010	Sales Year 2011
A	5,000	2,500
B	7,000	200
C	5,000	2,500
D	7,000	200

Could someone please advise how to do it?

Thanks.

Answer 1

You want to sum the CASE expressions here:

SELECT
    Item,
    SUM(CASE WHEN year = 2010 THEN sales ELSE 0 END) AS [Sales Year 2010],
    SUM(CASE WHEN year = 2011 THEN sales ELSE 0 END) AS [Sales Year 2011]
FROM yourTable
GROUP BY Item;