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

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

Solution 1:

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 three and is very short. It is not a complete walk-through but you can surely adapt the concept to your needs.

Solution 2:

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).