Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

Question

Current query:

SELECT order_id AS OrderNumber, ordName, ordLastName, question, answer 
from cart_survey 
JOIN orders 
ON cart_survey.order_id=orders.ordID 
JOIN survey_answers 
ON survey_answers.id=cart_survey.answer_id 
JOIN survey_questions 
ON survey_questions.id=cart_survey.question_id

Results:

OrderNumber ordName ordLastName question                answer
8591   Larry     Marshburn Type of Surgery:           Colostomy  
8591   Larry     Marshburn Month of Surgery:            2
8591   Larry     Marshburn Year of surgery:           2010
8591   Larry     Marshburn Current Ostomy System Brand:  ConvaTec  
8591   Larry     Marshburn Degree of Satisfaction:       Somewhat Satisfied  
8593   Melvin Belcher     Type of Surgery:           Urostomy
8593   Melvin Belcher     Month of Surgery:            9
8593   Melvin Belcher     Year of surgery:           2010
8593   Melvin Belcher     Current Ostomy System Brand:  ConvaTec  
8593   Melvin Belcher     Degree of Satisfaction:    Very Satisfied  

How do I properly query the tables to pull results that will look like this? Name and Lastname on a single line and questions for columns and answers for each column.

Desired Results

OrderNumber ordName ordLastName "Type of Surgery" "Month of Surgery" "Year of Surgery" etc.
8591        Larry   Marshbourn   Colostomy         2                  2010
8593        Melvin  Belcher      Urostomy          9                  2010

Answer 1

This is called a pivot, where information in rows is used to determine the list of columns. This sort of query requires dynamically-computed SQL if done entirely in a query, and is usually better suited to client-side formatting instead (many tools call it a pivot or cross-tab query, SSRS calls it a matrix query).

Answer 2

This is the MSSQL Version

    select o.*, q1.[Type of Surgery:], q2.[Month of Surgery:], q3.[Year of surgery:]
    , q4.[Current Ostomy System Brand:]
    , q5.[Degree of Satisfaction with the fit and comfort of your Current Ostomy System:]
    from (
    select distinct ordID, ordName + ' ' + ordLastName as [name] from dbo.Orders
    ) o
    left join (
    select *, a.[Answer] as [Type of Surgery:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 1
    ) q1 on o.ordID = q1.[order_id]
    left join (
    select *, a.[Answer] as [Month of Surgery:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 2
    ) q2 on o.ordID = q2.[order_id]
    left join (
    select *, a.[Answer] as [Year of surgery:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 3
    ) q3 on o.ordID = q3.[order_id]
    left join (
    select *, a.[Answer] as [Current Brand:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 4
    ) q4 on o.ordID = q4.[order_id]
    left join (
    select *, a.[Answer] as [Degree of Satisfaction:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 5
    ) q5 on o.ordID = q5.[order_id]

Answer 3

This is the MySQL Version

SELECT o . * , 
q1.answer AS  'Type of Surgery:',
q2.answer AS  'Month of Surgery:',
q3.answer AS  'Year of Surgery:',
q4.answer AS  'Current Brand:',
q5.answer AS  'Degree of Satisfaction:'
FROM (
SELECT DISTINCT ordID, ordName, ordLastName
FROM orders
)o
LEFT JOIN (
SELECT cs.order_id, a.answer
FROM cart_survey cs
LEFT JOIN survey_answers a ON cs.answer_id = a.id
WHERE cs.question_id =18
)q1 ON o.ordID = q1.order_id

LEFT JOIN (
SELECT cs.order_id, a.answer
FROM cart_survey cs
LEFT JOIN survey_answers a ON cs.answer_id = a.id
WHERE cs.question_id =19
)q2 ON o.ordID = q2.order_id

LEFT JOIN (
SELECT cs.order_id, a.answer
FROM cart_survey cs
LEFT JOIN survey_answers a ON cs.answer_id = a.id
WHERE cs.question_id =20
)q3 ON o.ordID = q3.order_id

LEFT JOIN (
SELECT cs.order_id, a.answer
FROM cart_survey cs
LEFT JOIN survey_answers a ON cs.answer_id = a.id
WHERE cs.question_id =21
)q4 ON o.ordID = q4.order_id

LEFT JOIN (
SELECT cs.order_id, a.answer
FROM cart_survey cs
LEFT JOIN survey_answers a ON cs.answer_id = a.id
WHERE cs.question_id =22
)q5 ON o.ordID = q5.order_id

Answer 4

The posted answers work but are clumsy and slow. You can do what I call parallel aggregation:

select

 ID,
 sum(case when question_id = 1 then 1 else 0 end) as sum1,
 sum(case when question_id = 2 then 1 else 0 end) as sum2,
 sum(case when question_id = 3 then 1 else 0 end) as sum3
group by ID

This will do one pass over the table instead of 3 and is very short. It is not a complete walk through but you can surely adapt the concept to your needs.