SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL View help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slihp
Yak Posting Veteran

60 Posts

Posted - 07/17/2013 :  05:19:25  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/17/2013 :  05:40:45  Show Profile  Reply with Quote

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


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

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 07/17/2013 :  05:52:44  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  06:13:26  Show Profile  Reply with Quote
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

60 Posts

Posted - 07/17/2013 :  07:00:20  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/17/2013 :  07:08:37  Show Profile  Reply with Quote
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

60 Posts

Posted - 07/17/2013 :  07:13:12  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/17/2013 :  07:18:33  Show Profile  Reply with Quote
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

60 Posts

Posted - 07/17/2013 :  07:28:21  Show Profile  Reply with Quote
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

60 Posts

Posted - 07/17/2013 :  07:36:08  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/17/2013 :  11:21:29  Show Profile  Reply with Quote
Oh ok...cool

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

slihp
Yak Posting Veteran

60 Posts

Posted - 07/18/2013 :  05:19:49  Show Profile  Reply with Quote
That wroked guys, thanks a lot...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000