创建从多行到一行的视图 [英] create view from multiple row into one row

问题描述

我有两张桌子；问答

问题表

QuestionID QuestionText
1          Question1
2          Question2
3          Question3

答案表.其中有fk到问题表和bit来判断答案是否正确

answer table. where it has fk to question table and bit to determine whether the answer is right

answerID answer_question_id(fk) answertxt answer_isright
1          1                    answer1   1
2          1                    answer2   0
3          1                    answer3   0
4          2                    answer1   1
5          2                    answer2   0
6          2                    answer3   0

那么如何创建第一列是问题而第二、第三和第四列是答案(随机)的视图?

so how do i create view where the first column is the question and second,third and fourth column is the answers (randomly)?

推荐答案

您可以使用PIVOT来解决您的问题:

You can use PIVOT to solve your problem:

SELECT questionText, [1], [2], [3]
FROM
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY newid()) AnswerInQuestionID,
        answerTxt, 
        QuestionText
    FROM questions q
        JOIN answers a
            ON q.QuestionID=a.answer_question_id
) A
PIVOT
(
    MAX(answerTxt)
    FOR AnswerInQuestionID IN ([1], [2], [3] )
) as piv

SQL 小提琴演示

这篇关于创建从多行到一行的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！