0

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?

5

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

  • I am extremely grateful. This worked perfectly as described and has really helped me. – HelloWorld Sep 5 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.