This has got to be a duplicate, but I simply cannot find one.
I am hoping to take a table that shows a person's name and what food/drinks they enjoy, with one row per person per item of food/drink (so multiple rows per person) and, after filtering for only the drinks, output a table that has exactly one row per person and a column for each drink in the dataset. The body of that table would then just be a boolean showing if that person enjoys that drink or not.
The key challenge here is that I want the columns to be determined by a query that draws all of the suitable columns from a dimension table. I don't want to type out the name of each desired column by hand, because the entries in the dimension table are subject to change.
How can I get T-SQL to handle this? A pivot table seems the obvious choice, but my knowledge of them indicates that I must do exactly what I don't want to do and type out the column names by hand.
Example input:
| Name | Enjoys |
|---|---|
| Alice | Coke |
| Alice | Biscuits |
| Alice | Lemonade |
| Bob | Coke |
| Bob | Apples |
| Charlie | Milk |
Assume also that we have a dimension table that records what is a food and what is a drink. I'll not insult your intelligence by typing its structure out. Don't worry too much about what to do with drinks that are in the dimension table but not the above list.
Desired output:
| Name | Coke | Lemonade | Milk |
|---|---|---|---|
| Alice | 1 | 1 | 0 |
| Bob | 1 | 0 | 0 |
| Charlie | 0 | 0 | 1 |
