CREATE VIEW yourViewName
AS
SELECT Val 
FROM
(
SELECT r.ID,q.Question,a.Answers
FROM Results r
INNER JOIN Question q
ON q.QuestionID = r.QuestionID 
INNER JOIN Answer a
ON a.AnswerID = r.AnswerID
AND a.QuestionID = r.QuestionID 
)t
UNPIVOT(Val FOR Descr IN (Question,Answers))u
ORDER BY ID

CREATE VIEW yourViewName
AS
SELECT TOP 100 PERCENT Val 
FROM
(
SELECT r.ID,q.Question,a.Answers
FROM Results r
INNER JOIN Question q
ON q.QuestionID = r.QuestionID 
INNER JOIN Answer a
ON a.AnswerID = r.AnswerID
AND a.QuestionID = r.QuestionID 
)t
UNPIVOT(Val FOR Descr IN (Question,Answers))u
ORDER BY ID

quote:

---

Originally posted by slihp

GREAT, thats exactly what i was after, thanks lots.

SELECT TOP 100 PERCENT Results
FROM
(
SELECT qr.ID, qr.InstanceID, q.QUESTION, qa.ANSWER
FROM dbo.HM_RAR_QUESTIONNAIRE_RESULTS qr
INNER JOIN dbo.HM_RAR_QUESTIONS q ON q.ID=qr.QuestionID
INNER JOIN dbo.HM_RAR_ANSWERS qa ON qa.ID=qr.AnswerID AND qa.QUESTION_ID=q.ID
WHERE
qr.QuestionID<>1
)t
UNPIVOT(Results FOR Descr IN (Question,Answer))u
ORDER BY ID


problem though i noticed when i implemented it, the results table holds all instances of all questionnaires. my applications form only want to show the question and answers relating to the questionnaire selected. the form can do this by storing an instance id that it gets passed. so i realy need to view to present the instanceid both the question and answer belong to in another column.

again is this possible?

---

quote:

---

Originally posted by slihp

InstnaceID is stored in the table, you set the form with a parameter, in this case instanceID, when the form is displayed it is passed the instanceID and uses this to filter out all the other questionnaire instance from the view.

---

CREATE PROC yourProcName
@InstanceID int
AS
SELECT TOP 100 PERCENT Results 
FROM
(
SELECT qr.ID, qr.InstanceID, q.QUESTION, qa.ANSWER
FROM dbo.HM_RAR_QUESTIONNAIRE_RESULTS qr
INNER JOIN dbo.HM_RAR_QUESTIONS q ON q.ID=qr.QuestionID
INNER JOIN dbo.HM_RAR_ANSWERS qa ON qa.ID=qr.AnswerID AND qa.QUESTION_ID=q.ID
WHERE
qr.QuestionID<>1 
)t
UNPIVOT(Results FOR Descr IN (Question,Answer))u
ORDER BY ID
GO

then call it like

EXEC YourProcName <InstanceIDvalue>

the <InstanceIDvalue> will be passed from your form