Skip to content
Advertisement

SQL Pivot and get column row data

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     


Advertisement

Answer

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;

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement