sql - How to retrieve same column twice with different conditions in same table?

Question

This is my table:

Anganbadi_ID               Food     Month
-------------------------------------------    
1165                       ???         1
1165                       ???         2
1165                       ???         4
1168                       ???         4
2032                       ????        4
2218                       ????        4
2219                       ???         4
2358                       ????        4
2546                        ???        10 

there are 4 columns Anganbadi_ID, Food, Month, Year and I want to compare Food column twice based on two different month values.

e.g. if I select Month=4 for first Food (Food-1) column and Month=10 for second Food (Food-2) column, then it should be like following::

Anganbadi_ID            Food-1     Food-2    
------------------------------------------    
1165                       ???          NULL 
1168                       ???          NULL 
2032                       ????        NULL 
2218                       ????        NULL 
2219                       ???          NULL 
2358                       ????        NULL 
2546                     NULL        ??? 

When I'm trying this code

SELECT     
   Anganbadi_ID, Food,
   (SELECT Food
    FROM Anganbadi AS Anganbadi_2
    WHERE (Anganbadi_1.Anganbadi_ID = Anganbadi_ID) 
      AND (Anganbadi_1.Month = 10)
   ) AS 'Food(2)'
FROM Anganbadi AS Anganbadi_1
WHERE (Month = 4)

It shows following results::

Anganbadi_ID              Food-1     Food-2    
--------------------------------------------
1165                       ???          NULL 
1168                       ???          NULL 
2032                       ????        NULL 
2218                       ????        NULL 
2219                       ???          NULL 
2358                       ????        NULL 

Please help me as soon as possible....

Answer 1

SELECT DISTINCT Anganbadi_ID,

(SELECT Food 
 FROM Anganbadi 
 WHERE      (Anganbadi_ID = A.Anganbadi_ID) 
    AND (Month = 4)) AS Food1,

(SELECT Food 
 FROM Anganbadi 
 WHERE      (Anganbadi_ID = A.Anganbadi_ID) 
    AND (Month = 10)) AS Food2

FROM Anganbadi AS A 
WHERE A.Month = 10 OR A.Month = 4

SQL Fiddle