Share via

Multiple Data fields from another table

CBooze 1 Reputation point
2022-01-13T21:26:42.66+00:00

I feel it may be PIVOT but I cant seem to identify how that would work for me. :P

But I have 3 tables:
Tasks: WO, Task, Staff
SurveyResponses: ID, WO
SurveyQuestionResponses: ID, SurveyResponseID, QuestionID, Response

Tasks.WO = SurveyResponses.WO
SurveyResponses.ID = SurveyQuestionResponses.SurveyResponseID

Now for each Task there is a SurveyResponses entry that links to SurveyQuestionResponses and within the SurveyQuestionResponses there are 5 entries, one for each question on the survey.
What I am attempting to do is a query that has the Task data with additional columns expanded. The problem I am experiencing is attempting to add the additional columns within the query for the responses.

SELECT T.WO,
    T.[TASK],
        question8????,
        question9????,
        question22????,
        question23????,
FROM [TASKS] T
WHERE T.[STAFF] IN (SELECT [NAME] FROM [STAFF] WHERE EID IS NOT NULL)

Here's a little example of what I have, it gets me all the task info, but I am looking for the question mark areas, so basically the response where SurveyQuestionResponses.QuestionID = 8, 9, 22, 23.

Let me know if I am missing any info here I will respond ASAP, thank you in advance for any guidance. :)

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.