穷人的 SQL 支点.在一行中列出每个用户的问题作为列和答案 [英] Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

问题描述

当前查询:

SELECT order_id AS OrderNumber, ordName, ordLastName, question, answer来自cart_survey加入订单开启cart_survey.order_id=orders.ordID加入survey_answersONsurvey_answers.id=cart_survey.answer_id加入调查_问题ONsurvey_questions.id=cart_survey.question_id

结果:

<前>订单号 ordName ordLastName 问题答案8591 Larry Marshburn 手术类型:结肠造口术8591 Larry Marshburn 手术月数:28591 Larry Marshburn 手术年份:20108591 Larry Marshburn 当前造口系统品牌:ConvaTec8591 Larry Marshburn 满意程度:有点满意8593 Melvin Belcher 手术类型:尿道造口术8593 Melvin Belcher 手术月数:98593 梅尔文·贝尔彻 手术年份:20108593 Melvin Belcher 当前造口系统品牌:ConvaTec8593 Melvin Belcher 满意程度:非常满意

如何正确查询表以提取如下所示的结果?姓名和姓氏在一行中，列的问题和每列的答案.

预期结果

<前>OrderNumber ordName ordLastName 手术类型" 手术月份" 手术年份"等.8591 拉里·马什伯恩结肠造口术 2 20108593 梅尔文贝尔彻尿道造口术 9 2010

解决方案

这是 MSSQL 版本

选择o.*，q1.[手术类型:]，q2.[手术月份:]，q3.[手术年份:], q4.[当前造口系统品牌:], q5.[对您当前造口系统的适合性和舒适度的满意度:]从 (从 dbo.Orders 中选择不同的 ordID, ordName + ' ' + ordLastName 作为 [name]) o左连接(从cart_survey cs 中选择*, a.[Answer] 作为[Type of Surgery:]左加入 dbo.survey_answers a on cs.answer_id = a.id其中 cs.question_id = 1) q1 on o.ordID = q1.[order_id]左连接(select *, a.[Answer] as [Month of Surgery:] from car_survey cs左加入 dbo.survey_answers a on cs.answer_id = a.id其中 cs.question_id = 2) q2 on o.ordID = q2.[order_id]左连接(从cart_survey cs 中选择*, a.[Answer] 作为[手术年份:]左加入 dbo.survey_answers a on cs.answer_id = a.id其中 cs.question_id = 3) q3 on o.ordID = q3.[order_id]左连接(select *, a.[Answer] as [Current Brand:] from cart_survey cs左加入 dbo.survey_answers a on cs.answer_id = a.id其中 cs.question_id = 4) q4 on o.ordID = q4.[order_id]左连接(select *, a.[Answer] as [Degree of Satisfaction:] from cart_survey cs左加入 dbo.survey_answers a on cs.answer_id = a.id其中 cs.question_id = 5) q5 on o.ordID = q5.[order_id]

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

解决方案

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]

这篇关于穷人的 SQL 支点.在一行中列出每个用户的问题作为列和答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！