5600

create view from multiple row into one row

Question:

i have two table; question and answer

question table

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, [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

<strong><a href="http://sqlfiddle.com/#!3/a1739/1" rel="nofollow">SQL FIDDLE DEMO</a></strong>

Answer2:

next query

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

Recommend

  • value not updated to textbox in Jquery of MVC3 view
  • Unity C# How to set text as XML component?
  • Unity: Remove The selected object from random.range
  • Create another model objects in django admin within a for loop
  • Integrating keyboard use with answer randomization in Qualtrics
  • jQuery - How to separate two objects which are dependent on same object?
  • How do you get values from three different tables using linq to sql?
  • Mongoose - increment a field value in nested array
  • How to get the IDENTITY value when using INSERT … OUTPUT with pyodbc
  • How to get a part of id element using jQuery?
  • Store the result of a stored procedure without using an output parameter
  • SQL Server how to maintain GUID across tables in same DB
  • SQL Keeping count of occurrences
  • How select second line from top 2 or something similar?
  • jQuery & CSS - Cut text by height, no truncate
  • Retrieve List of Cells in Excel Where Column Equals Something
  • I got jquery errors randomly
  • SQL Server PIVOT Function
  • how to pivot complex dataframe
  • Is storing an OAuth token in cookies bad practise?
  • How to generate random events in android?
  • random selection within groups
  • Row_number() function for Informix
  • Is there a way to pivot a customer ID and a their most recent order dates?
  • How can I encode a filename according to RFC 2231?
  • matlab/octave random event ode45
  • Underlying type for Tuple in Swift
  • Build Matrix of Comparisons in SQl Server
  • Azure table query partial partitionkey guid match
  • Create function that can pass a parameter without making a new component
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • How can we prepend rows to a react native list-view?
  • Merging rows to columns
  • Breeze - Deleted Items nav properties bug
  • javaw.exe and eclipse startup problems
  • Hazelcast - OperationTimeoutException
  • ORA-29908: missing primary invocation for ancillary operator
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • apache spark aggregate function using min value