Join one-to-many field in a single result without concatenation
One way you can do this is with a PIVOT.

Here is the setup code:

create table grades (
  ID             int,
  Mapped_Heading varchar(10),
  Source_Value   varchar(10)
);
create table students (
  ID   int,
  Name varchar(10),
  Age  int
);
insert grades 
values
(1,'English', 'pass'),
(1,'Math',    'pass'),
(1,'History', 'fail');
insert students
values
(1, 'Bob',   12),
(2, 'Harry', 12),
(3, 'Emily', 14);


Here is the PIVOT code:

with temp as (
  select s.*, g.Mapped_Heading, g.Source_Value
  from students s
  join grades g on g.ID = s.ID
)
select * 
from (
    select * 
    from temp
) a
pivot (
    max(Source_Value)
    for Mapped_Heading in ([English],[Math],[History])
) as b


The output looks like this:

+----+------+-----+---------+------+---------+
| ID | Name | Age | English | Math | History |
+----+------+-----+---------+------+---------+
| 1  | Bob  | 12  | pass    | pass | fail    |
+----+------+-----+---------+------+---------+


db&lt;>fiddle demo