SELECT a.id, a.clientName, SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MONTH,b.GoDate)='1' THEN c.Hj ELSE 0 END) AS [1月份], SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MONTH,b.GoDate)='2' THEN c.Hj ELSE 0 END) AS [2月份], SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MONTH,b.GoDate)='3' THEN c.Hj ELSE 0 END) AS [3月份] ... FROM ClientTable AS a LEFT JOIN (CoderTable AS b INNER JOIN JpddTable as c ON c.DdId=b.id) ON a.id=c.ClientId
a.id, a.clientName,
SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MONTH,b.GoDate)='1' THEN c.Hj ELSE 0 END) AS [1月份],
SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MONTH,b.GoDate)='2' THEN c.Hj ELSE 0 END) AS [2月份],
SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MONTH,b.GoDate)='3' THEN c.Hj ELSE 0 END) AS [3月份]
...
FROM
ClientTable AS a
LEFT JOIN (CoderTable AS b INNER JOIN JpddTable as c ON c.DdId=b.id) ON a.id=c.ClientId