(SQL) Query Results on one row

Question

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 1

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