-2

How do I merge multiple response Ids into 1 row, so I can left join with another table later on?

Data:

[enter image description here

Goal: goal:

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
msulol
  • 67
  • 6
  • 1
    Not sure why you think merging records in this way affects the ability to join tables? Denormalising data in the way you suggest is normally a bad idea – NickW May 31 '21 at 19:46

1 Answers1

0

In general there are two strategies: joining, or aggregating.

I include the first option here. For example, you can do:

select
  a.caseno,
  a.q_id, a.question_name, a.question_response,
  b.q_id, b.question_name, b.question_response,
  c.q_id, c.question_name, c.question_response,
  d.q_id, d.question_name, d.question_response
from t a
join t b on b.caseno = a.caseno and b.q_id = 'ID2'
join t c on c.caseno = a.caseno and c.q_id = 'ID3'
join t d on d.caseno = a.caseno and d.q_id = 'ID4'
where a.q_id = 'ID1'
The Impaler
  • 45,731
  • 9
  • 39
  • 76