Join one-to-many field in a single result without concatenation

Question

I suffer from the same problem as in Concatenate one-to-many field in single result?, but I need a result where the 'many' fields occupy their own columns and aren't concatenated in one column. Any suggestions?

Sample data:

___ID      Mapped_Heading    Source_Value
1          English           pass
1          Math              pass
1          History           fail


___ID      Name              Age
1          Bob               12
2          Harry             12
3          Emily             14

And the End result would be:

___ID      Name      Age  English   Math   History
1          Bob       12   pass      pass   fail

I was hoping I wouldn't have to use PIVOT and PARTITION BY because I'm not familiar with those functions, but I keep bumping into them for this problem so think I might have to?

score 5 · Accepted Answer · edited Sep 05 '19 at 12:38

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<>fiddle demo

Join one-to-many field in a single result without concatenation

1 Answers1