SQL Server - Trying to de-normalize my table

Question

Apologies for the title but I am trying to do stuff way above my level even for me to explain it.

Say I have a table with the variables person, foodstuff and amount:

 Person  food     Amount
 Mike    Butter   3
 Mike    Milk     4
 Mike    Chicken  2
 Tim     Milk     4
 John    Chicken  2

By joining the table with itself in the query I have managed to make a list where the food is the basis for new variables and the value is the amount. The above table would become:

Person  Butter Milk Chicken
Mike    3      4    2

The code is approximately:

Select 
    a.person, 
    b.amount as Butter,
    c.amount as Milk, 
    d.amount as Chicken
from PersonFoodAmount a
inner join PersonFoodAmount b on a.person = b.person
inner join PersonFoodAmount c on a.person=c.person
where b.food='Butter' 
and c.food='Milk'
and d.food='Chicken'

Now, this gives me Mike because he checks all boxes off. But I need to also have the partial matches:

Person  Butter Milk Chicken
Mike    3      4    2
Tim     NULL   4    NULL
John    NULL   Null 2

I have tried all kinds of joins, including full outer join but I still only get persons with a full fridge.

Any suggestions?

Answer 1

You can use Pivot for make this.

DECLARE @PersonStuff TABLE (Person varchar(10), Food varchar(10), Amount INT)

INSERT INTO @PersonStuff VALUES
('Mike','Butter', 3),
('Mike','Milk', 4),
('Mike','Chicken', 2),
('Tim','Milk', 4),
('John','Chicken', 2)

SELECT 
    * 
FROM ( 
    SELECT 
        * 
    FROM @PersonStuff ) AS SourceTable
PIVOT ( 
    AVG(Amount) 
    FOR Food IN ( [Butter],[Milk],[Chicken] ) 
) AS PivotTable

Result:

Person  Butter  Milk    Chicken
John    NULL    NULL    2
Mike    3       4       2
Tim     NULL    4       NULL