SQL Pivot and get column row data

Question

I have tables and data as below. Trying to get the Manufacturer and the questions they answered by joining and pivot but able to. Added the expected output at the end but no luck. Any suggestions are appreciated.

tblManufacturer

MFRID	MFRNum
1	3M
2	GM
3	HD
4	GL

tblMFRQuestions

MFRQID	MFRQTEXT
21	ENTER PRICE??
22	WHAT IS TIME??
23	WHAT IS RANGE??

tbMFRQuestionAnswers

MFRID	MRFRQID	MFRANSWER
1	21	55
1	22	9AM
1	23	105KM
2	21	57
2	22	10PM
2	23	535KM
3	21	355
3	22	12AM
3	23	5105KM

Expected Output:

MFRID MFRNUM  ENTER PRICE?? WHAT IS TIME??   WHAT IS RANGE??
1      3M       55              9AM             105KM
2      GM       57              10PM            535KM
3      HD       355             12AM            5105KM

Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Charlieface, Jan 25 '21 at 22:43

score 2 · Accepted Answer · answered Jan 25 '21 at 22:17

You could try something like this

select m.*,
       max(case when q.MFRQTEXT='ENTER PRICE??' then qa.MFRANSWER else null end) [ENTER PRICE??],
       max(case when q.MFRQTEXT='WHAT IS TIME??' then qa.MFRANSWER else null end) [WHAT IS TIME??],
       max(case when q.MFRQTEXT='WHAT IS RANGE??' then qa.MFRANSWER else null end) [WHAT IS RANGE??]
from tblManufacturer m
     join tbMFRQuestionAnswers qa on m.MFRID=qa.MFRID
     join tblMFRQuestions q on qa.MFRQID=q.MFRQID
group by m.MFRID, m.MFRNum
order by m.MFRID, m.MFRNum;

SQL Pivot and get column row data

1 Answers1