SQL query - comparing two items on distinct item

Hi I'm a newby to SQL code and wondered if it was possible to get an output of:-

**EVT   John    Paul    Difference**
A1      1       2       -1
A2      2       3       -1

---

From the bel开发者_如何学编程ow data source.

**EVT   PERS    RANK**
A1      John    1
A1      Paul    2
A1      Ringo   3
A1      George  4
A2      Ringo   1
A2      John    2
A2      Paul    3
A2      George  4

---

---

SELECT 
      EVT,
      MAX(CASE WHEN Pers='John' THEN Rank END) AS John,
      MAX(CASE WHEN Pers='Paul' THEN Rank END) AS Paul,
      MAX(CASE WHEN Pers='John' THEN Rank END) -  
                  MAX(CASE WHEN Pers='Paul' THEN Rank END) as Difference
FROM YourTable 
WHERE Pers IN ('John','Paul')
GROUP BY EVT