Need help in getting Average using SQL Cross Tab

[dfs3000my]

Hi all,

I have managed to get my average using cross tabs but only if I have grouped it by their categories. If I remove the GROUP BY clause, the cross tabbing doesn't seem to work. Can anyone help me? I would need to get something like:-

Year | Month | Benefits Rating | Facilities Rating
-----------------------------------------------------
2007 | December | 2 | 3
2008 | November | 3 | 3
2008 | October  | 2 | 1
2008 | September| 2 | 2

Thanks

p/s: example is shown as an image below.

[huangzhi]

Make it subquery like below:

select [Year], [Month], sum([Benefits Rating]) as [Benefits Rating], sum([Facilities Rating]) as [Facilities Rating]
  from (...... your origin query ......) X
  group by [Year], [Month]

[dfs3000my]

Hi,

I've tried removing the question.category_id from the GROUP BY clause but AVG() doesn't seem to work. It works perfectly fine for SUM() but not AVG().

Below is how the output looks without the question.category_id in the GROUP BY clause.

[ATTACH]8701[/ATTACH]

[dfs3000my]

I have managed to find a solution to this problem

SELECT     DATEPART(year, q.attempted_date) AS Year, DATENAME(MM, q.attempted_date) AS Month, 
                      AVG(CASE WHEN w.category_id = 1 THEN CAST(SUBSTRING(a.answer, 4, 1) AS int) ELSE NULL END) AS [Average Benefits Rating], 
                      AVG(CASE WHEN w.category_id = 2 THEN CAST(SUBSTRING(a.answer, 4, 1) AS int) ELSE NULL END) AS [Average Facilities Rating]
FROM         questionnaire AS q INNER JOIN
                      employee AS e ON q.employee_id = e.employee_number INNER JOIN
                      answer AS a ON e.id = a.employee_id INNER JOIN
                      question AS w ON a.question_id = w.id
WHERE     (w.question_type = 1) AND (a.answer <> 'ansNA') AND (q.attempted_date IS NOT NULL)
GROUP BY DATEPART(year, q.attempted_date), DATENAME(MM, q.attempted_date)
ORDER BY Year, Month DESC