SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Preferences (
  "Student_ID" INTEGER,
  "Student_Name" VARCHAR(8),
  "Meat" INTEGER,
  "Vegetable" INTEGER,
  "Drink" INTEGER,
  "Dessert" INTEGER
);

INSERT INTO Preferences
  ("Student_ID", "Student_Name", "Meat", "Vegetable", "Drink", "Dessert")
VALUES
  ('1', 'Jeff', '3', '1', '4', '1'),
  ('2', 'Andrea', '1', '1', '3', '1'),
  ('3', 'Allison', '3', '2', '7', '1'),
  ('4', 'Randy', '1', '1', '4', '2'),
  ('5', 'Carl', '2', '5', '2', '2'),
  ('6', 'Bobby', '1', '6', '7', '2'),
  ('7', 'Julie', '3', '5', '2', '1'),
  ('8', 'Anna', '1', '6', '7', '2'),
  ('9', 'Carlos', '1', '6', '2', '2'),
  ('10', 'Roger', '2', '4', '2', '1'),
  ('11', 'Pierre', '1', '2', '1', '1'),
  ('12', 'Troy', '2', '3', '3', '1'),
  ('13', 'David', '3', '6', '6', '2'),
  ('14', 'Michaela', '1', '4', '5', '2'),
  ('15', 'Rose', '1', '4', '6', '1'),
  ('16', 'Anita', '3', '6', '6', '2'),
  ('17', 'Connor', '3', '3', '3', '1'),
  ('18', 'Eddie', '1', '2', '7', '1'),
  ('19', 'Karen', '3', '5', '5', '2'),
  ('20', 'Rachel', '3', '2', '2', '1');
20 rows affected

CREATE TABLE food (
  "Item_Type" VARCHAR(9),
  "Item" VARCHAR(8),
  "Value" INTEGER
);

INSERT INTO food 
  ("Item_Type", "Item", "Value")
VALUES
  ('Meat', 'Chicken', '1'),
  ('Meat', 'Beef', '2'),
  ('Meat', 'Fish', '3'),
  ('Vegetable', 'Carrots', '1'),
  ('Vegetable', 'Peas', '2'),
  ('Vegetable', 'Corn', '3'),
  ('Vegetable', 'Broccoli', '4'),
  ('Vegetable', 'Zucchini', '5'),
  ('Vegetable', 'Eggplant', '6'),
  ('Drink', 'Water', '1'),
  ('Drink', 'Milk', '2'),
  ('Drink', 'Juice', '3'),
  ('Drink', 'Cola', '4'),
  ('Drink', 'Lemonade', '5'),
  ('Drink', 'Tea', '6'),
  ('Drink', 'Punch', '7'),
  ('Dessert', 'Cake', '1'),
  ('Dessert', 'Pie', '2');
18 rows affected

select  *
from   (select  item
               ,food_type
               ,Student_ID
               ,Student_Name
        from    Preferences as p
        unpivot (food for food_type in (Meat,Vegetable,Drink,Dessert)) as u 
        join food on food.value =  u.food and  food.Item_Type = u.food_type) as pu
        pivot (max(item) for food_type in(Meat, Vegetable, Drink, Dessert)) as p
order by Student_ID



Student_ID
Student_Name
Meat
Vegetable
Drink
Dessert


1
Jeff
Fish
Carrots
Cola
Cake

2
Andrea
Chicken
Carrots
Juice
Cake

3
Allison
Fish
Peas
Punch
Cake

4
Randy
Chicken
Carrots
Cola
Pie

5
Carl
Beef
Zucchini
Milk
Pie

6
Bobby
Chicken
Eggplant
Punch
Pie

7
Julie
Fish
Zucchini
Milk
Cake

8
Anna
Chicken
Eggplant
Punch
Pie

9
Carlos
Chicken
Eggplant
Milk
Pie

10
Roger
Beef
Broccoli
Milk
Cake

11
Pierre
Chicken
Peas
Water
Cake

12
Troy
Beef
Corn
Juice
Cake

13
David
Fish
Eggplant
Tea
Pie

14
Michaela
Chicken
Broccoli
Lemonade
Pie

15
Rose
Chicken
Broccoli
Tea
Cake

16
Anita
Fish
Eggplant
Tea
Pie

17
Connor
Fish
Corn
Juice
Cake

18
Eddie
Chicken
Peas
Punch
Cake

19
Karen
Fish
Zucchini
Lemonade
Pie

20
Rachel
Fish
Peas
Milk
Cake

Student_ID	Student_Name	Meat	Vegetable	Drink	Dessert
1	Jeff	Fish	Carrots	Cola	Cake
2	Andrea	Chicken	Carrots	Juice	Cake
3	Allison	Fish	Peas	Punch	Cake
4	Randy	Chicken	Carrots	Cola	Pie
5	Carl	Beef	Zucchini	Milk	Pie
6	Bobby	Chicken	Eggplant	Punch	Pie
7	Julie	Fish	Zucchini	Milk	Cake
8	Anna	Chicken	Eggplant	Punch	Pie
9	Carlos	Chicken	Eggplant	Milk	Pie
10	Roger	Beef	Broccoli	Milk	Cake
11	Pierre	Chicken	Peas	Water	Cake
12	Troy	Beef	Corn	Juice	Cake
13	David	Fish	Eggplant	Tea	Pie
14	Michaela	Chicken	Broccoli	Lemonade	Pie
15	Rose	Chicken	Broccoli	Tea	Cake
16	Anita	Fish	Eggplant	Tea	Pie
17	Connor	Fish	Corn	Juice	Cake
18	Eddie	Chicken	Peas	Punch	Cake
19	Karen	Fish	Zucchini	Lemonade	Pie
20	Rachel	Fish	Peas	Milk	Cake