(SQL) Query Results On One Row
So I have two tables, named "Questions" and Answers
I made a INNER JOIN for those two tables
SELECT Questions.ID, Questions.QText, Answers.AText
FROM Questions INNER JOIN Answers
ON Questions.ID=Answers.QuestionID;
And the result looks like this:
However, you can clearly see, that the question is displayed 4 times each time with on of the answers.
Now My question is: Is it possible to have the result in one row, consisting of
[ID] - [Question] - [Answer1] - [Answer2] - [Answer3] - [Answer4]
Answer
You could use this Query for a Pivot:
SELECT QID, QText, [1], [2], [3], [4]
FROM (SELECT Q.ID AS QID, A.ID AS AID, Q.QText, A.AText FROM Questions AS Q
INNER JOIN Answers AS A ON Q.ID = A.QuestionID) QA
PIVOT
(
MAX(AText)
FOR AID
IN ([1], [2], [3], [4])
) AS PV
BUT: You must use a combined Primary Key for your Answer Table.
With this Pivot Table the answer ID must be 1, 2, 3, 4 every time. Otherwise you don't get your answers.
FOR AID
declares, which column is used for this.
Otherwise you could add a column to answers which called "AnswerNr" or something like that. In this column you put 1 to 4 for your answers and change the query to:
SELECT QID, QText, [1], [2], [3], [4]
FROM (SELECT Q.ID AS QID, ->A.AnswerNr<-, Q.QText, A.AText FROM Questions AS Q
INNER JOIN Answers AS A ON Q.ID = A.QuestionID) QA
PIVOT
(
MAX(AText)
FOR ->AnswerNr<-
IN ([1], [2], [3], [4])
) AS PV
Related Questions
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?