Rows in their own columns depending on their date and symbolized by 'x'

Dear All, please help me since I'm newbie in SQL Server. I have a select query that currently produces the following results:

DoctorName Team Visit date

dr. As A 5

dr. Sc A 4

dr. Gh B 6

dr. Nd 开发者_如何学运维C 31

dr As A 7

Using the following query: SELECT d.DoctorName, t.TeamName, ca.VisitDate FROM cActivity AS ca INNER JOIN doctor AS d ON ca.DoctorId = d.Id INNER JOIN team AS t ON ca.TeamId = t.Id WHERE ca.VisitDate BETWEEN '1/1/2010' AND '1/31/2010'

I want to produce the following:

DoctorName Team 1 2 3 4 5 6 7 ... 31 Visited

dr. As   A                             x    x ...      2 times

dr. Sc   A                          x          ...      1 times

dr. Gh   B                                  x ...      1 times

dr. Nd   C                                  ... x      1 times

---

Use:

  SELECT d.doctorname,
         t.teamname,
         MAX(CASE WHEN ca.visitdate = 1 THEN 'x' ELSE NULL END) AS 1,
         MAX(CASE WHEN ca.visitdate = 2 THEN 'x' ELSE NULL END) AS 2,
         MAX(CASE WHEN ca.visitdate = 3 THEN 'x' ELSE NULL END) AS 3,
         ...
         MAX(CASE WHEN ca.visitdate = 31 THEN 'x' ELSE NULL END) AS 31,
         COUNT(*) AS visited
    FROM CACTIVITY ca
    JOIN DOCTOR d ON d.id = ca.doctorid 
    JOIN TEAM t ON t.id = ca.teamid
   WHERE ca.visitdate BETWEEN '1/1/2010' AND '1/31/2010'
GROUP BY d.doctorname, t.teamname