I have a table that like below:

foo\_table

![enter image description here](https://i.stack.imgur.com/Sf8oL.png)

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

```

![enter image description here](https://i.stack.imgur.com/QQVcK.png)

How to group them by [DepartmentName]?