-1

How can I combine two tables using the row data of table 2 as column header?

table 1:

id email firstname middlename lastname
2901 john@io.com john michael smith

table 2:

id questionId answer
2901 1 Toronto
2901 2 Canada
2901 3 9991118721

Final result should be table 1 headers plus questionId data as header, then using table 1 data and table 2 answer data as rows:

id email firstname middlename lastname 1 2 3
2901 john@io.com john michael smith Toronto Canada 9991118721
Dale K
  • 25,246
  • 15
  • 42
  • 71
Belle
  • 1
  • 1
  • Have a look at the PIVOT/UNPIVOT functions – Martin Cairney Feb 02 '21 at 23:08
  • 1
    Only with Dynamic SQL if you need the column names to be the value of the question id dynamically. I would personally suggest you perhaps rethink about this is you *really* "need" this. If you do *really* "need" this, then see [SQL Server dynamic PIVOT query?](https://stackoverflow.com/q/10404348/2029983). If you don't understand the solution, then I also suggest rethinking; dynamic SQL isn't something you should be using unless you understand it. – Thom A Feb 02 '21 at 23:09

1 Answers1

0

You could try something like this

select t1.id, t1.email, t1.firstname, t1.middlename, t1.lastname,
       max(case when t2.questionId=1 then t2.answer else null end) 1,
       max(case when t2.questionId=2 then t2.answer else null end) 2,
       max(case when t2.questionId=3 then t2.answer else null end) 3
from table1 t1
     join table2 t2 on t1.id=t2.id
group by t1.id, t1.email, t1.firstname, t1.middlename, t1.lastname;
SteveC
  • 5,955
  • 2
  • 11
  • 24