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 7Using 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 timesUse:
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
精彩评论