SQL Server - attempt to de-normalize a table

Question

Apologies for the title, but I'm trying to do something much higher than my level, even to explain it.

Let's say I have a table with variables person, food and quantity:

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

By joining the table with myself in the query, I managed to make a list in which food is the basis for new variables, and the value is the quantity. Table above:

 Person Butter Milk Chicken Mike 3 4 2 

Code 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 it gives me Mike, because he checks all the boxes. But I also need to have partial matches:

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

I tried all kinds of pools, including full outer join, but I still only get people with a full fridge.

Any suggestions?

Answer 1

You can use Pivot for 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 

Answer 2

I would suggest something better, conditional aggregation:

 SELECT t.person, MAX(CASE WHEN t.food = 'Butter' THEN t.amout END) as Butter, MAX(CASE WHEN t.food = 'Milk' THEN t.amout END) as Milk, MAX(CASE WHEN t.food = 'Chicken' THEN t.amout END) as Chicken FROM PersonFoodAmount t GROUP BY t.person 

Thus, you do not have to join the table 3 times to yourself. In addition, I find that this letter is much easier to read once in order to understand this.