i have two table; question and answer
QuestionID QuestionText 1 Question1 2 Question2 3 Question3
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)?Answer1:
You can use
PIVOT to solve your problem:
SELECT questionText, , ,  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 (, ,  ) ) as piv
<strong><a href="http://sqlfiddle.com/#!3/a1739/1" rel="nofollow">SQL FIDDLE DEMO</a></strong>Answer2:
SELECT answerid, answer_question_id, answer_isright, row_number() over (partition by answer_question_id order by newid()) as rnum from answers
will return you your answer table with extra column to represent what column that answer goes to. "order by newid()" is not part of a standard and is different for every database vendor.
answerId .... rnum 1 1 2 3 3 2 4 3 5 1 6 2
(rnums would be different for every execution)
then you use this query to move answers to different columns based on rnum
select answerid, ..., case when rnum = 1 then answertxt else null end co1, ...
this will move your text like this:
answerId .... rnum ... col1, col2 col3 1 1 text1 null null 2 3 null null text2 3 2 null text3 null 4 3 null null text4 5 1 text5 null null 6 2 null text6 null
then you need to group them:
select answer_question_id, .., max(col1), max(col2), max(col3) from prev_query group by answer_question_id, ...
then you join with a questions to add question text