Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL View help

Author  Topic 

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-07-17 : 05:19:25
We have a risk assessment questionnaire program this feeds off a Question table and an Answer table and save into a results table.

Question table
-----------------------
ID | Question
-----------------------
1 | Question 1
2 | Question 2
3 | Question 3
4 | Question 4
5 | Question 5

Answer table
-----------------------
ID |QuestionID |Answers
-----------------------
1 | 1 |Yes
2 | 1 |NO
3 | 2 |True
4 | 2 |False
5 | 3 |Pass
6 | 3 |Refer
7 | 3 |Fail
8 | 4 |Mostly
9 | 4 |Never
10 | 5 |Blue
11 | 5 |Green

Results table
--------------------------
ID | QuestionID | AnswerID
-----------------------
1 | 1 | 2
2 | 2 | 4
3 | 3 | 7
4 | 4 | 8
5 | 5 | 11


The user is presented with a series of questions and answers. Each question can have one answer from a possible two or three. I want to present the user with questionnaire results in the below format (i.e. question with answer below)

Question 1
No
Question 2
False
Question 3
Fail
Question 4
Mostly
Question 5
Green

Problem is the application I have to use to present the results will only read from a SQL view. I don’t use views to much but im aware they have limitations to what you can do. Is it possible to construct sql for a view that will present the results in the format I want?

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 05:40:45
[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-17 : 05:52:44
you can't have the ORDER BY in a VIEW, unless TOP is used
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



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 06:13:26
Yep..thats true..that problem with fitting the CREATE VIEW part just before posting


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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-07-17 : 07:00:20
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 07:08:37
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?


Its possible. Is instanceid one generated by form or is it stored in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-07-17 : 07:13:12
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 07:18:33
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.


Oh ok...You cant pass parameters to a view
For that you need to make it into a procedure like



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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-07-17 : 07:28:21
No no i knew that.

I dont need to pass anything to the view. as long as the view has an InstancedID colum exposing the instanceid for each question and answer the form some how manages to filter out the other results (i can link the parameter passed to the form to the instanceid exposed by the view and the form dose the rest internally)

the form is an asp page created by our third party vendor, it can only run off view, i cant use sp's its rubbish but i have to use it :-(.

so if i can get the view to display another colum showing the instanceid each question and answer im sure this would work as i intended!!
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-07-17 : 07:36:08
so would this work, it seems to give me the output im after

SELECT InstanceID, 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))AS u
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 11:21:29
Oh ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-07-18 : 05:19:49
That wroked guys, thanks a lot...
Go to Top of Page
   

- Advertisement -