convert row to column and group by key

Question

I have a table that like below:

foo_table

What i'm trying to archive is some thing like:

[DepartmentName] , [4] , [5] , [6]

Below is what i tried:

select DepartmentName,
CASE foo_table.keyid WHEN '4' THEN foo_table.Score END AS [4],
CASE foo_table.keyid WHEN '5' THEN foo_table.Score END AS [5],
CASE foo_table.keyid WHEN '6' THEN foo_table.Score END AS [6]
     from foo_table

How to group them by [DepartmentName]?

Answer 1

select DepartmentName,
max(CASE foo_table.keyid WHEN '4' THEN foo_table.Score END) AS [4],
max(CASE foo_table.keyid WHEN '5' THEN foo_table.Score END) AS [5],
max(CASE foo_table.keyid WHEN '6' THEN foo_table.Score END) AS [6]
     from foo_table
group by DepartmentName;