I'm currently using SQL Server 2016 to select from a table to output a different layout. The following table `item_stats` :

```
code_no | code_status | code_category
--------+-------------+--------------
a123    |      x      | error1
a123    |      y      | error1
a123    |      z      | error1
a123    |      x      | error1
a123    |      y      | error2
b123    |      x      | error2
b123    |      y      | error2
c123    |      y      | error1
c123    |      z      | error1

```

I'm trying using group by or even partition by but cant get a result. I would like to get the following output:

```
code_no | x | y | z | code_category
--------+---+---+---+---------------
a123    | 2 | 1 | 1 | error1
a123    | 0 | 1 | 0 | error2
b123    | 1 | 1 | 0 | error2
c123    | 0 | 1 | 2 | error1

```

Basically the result will group by the `code_no` and `code_category` and showing each `code_status` which is x,y and z as a column and show the number of occurrences within those columns.

Appreciate any help or guidance in getting the output. I have tried using group by but the layout does not split the code status as an individual column.

Thanks
A. Fai