SQL to pivot a field with CASE WHEN

My table has fields: Item, AttributeNo_, AttrbuteValue. Each AttributeNo_ has a matched AttributeName.

e.g.

Item, AttributeNo_, AttributeValue
A,    1,            Yellow
A,    2,            Round
……

(AttributeNo_ 1 means color, 2 means shape as AttributeName)

This is what I’m trying to achieve:

Item, Color,  Shape
A,    Yellow, Round
……

My code is:

select Item, 
case when AttributeNo_=1 then AttributeValue end AS Color,
case when AttributeNo_=2 then AttributeValue end AS Shape
from table;

The result is like:

Item, Color,  Shape
A,    Yellow, Null
A,    Null,   Round
……

How can I achieve the correct result?

Thanks in advance!

Answer

Use aggregation:

select Item, 
       max(case when AttributeNo_ = 1 then AttributeValue end) AS Color,
       max(case when AttributeNo_ = 2 then AttributeValue end) AS Shape
from table
group by Item;