How to display data in this format

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)

How to display data in this format

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2009-09-02 : 00:19:20

Dear All,
I am using sql server 2005.
I have three tables question , question_option , Exam_answer. Format of the table are

Question Table
Qid Question Other detail
1 What is your name Xyz
2 Where do u reside abc

Question_option
qid opno option Iscorrect
1 1 Imran True
1 2 khalid False
1 3 raju False
1 4 rahil False
2 1 malad True
2 2 bandra False
2 3 Mahim false

Exam_answer is where user response is saved during the exam. And is like
pid Qid opno usrid corrct ISattempt
1 1 2 User1 false True
1 2 1 User1 true True
1 1 1 User2 true True
1 2 2 User2 false True
1 1 User3 false False
1 2 2 User3 false True

Now I want to find wrong answer given by user for that paper.

That is
For paper 1 user1 has given wrong answer for qid 1. So I want to display
question Response given Isattempt Correct ans
What is your name Khalid True imran

Similarly paper 1 user2 has given wrong answer for qid 2 that should display

question Response given Isattempt Correct ans
Where do u reside Bandra True malad

Please suggest how to achieve

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-02 : 01:06:20

[code]
declare @Question Table
(
Qid int,
Question varchar(30),
Other_detail varchar(30)
)
insert into @Question
select 1, 'What is your name', 'Xyz' union all
select 2, 'Where do u reside', 'abc'

declare @Question_option table
(
Qid int,
Optionno int,
[Option] varchar(10),
Iscorrect varchar(10)
)
insert into @Question_option
select 1, 1, 'Imran', 'True' union all
select 1, 2, 'khalid', 'False' union all
select 1, 3, 'raju', 'False' union all
select 1, 4, 'rahil', 'False' union all
select 2, 1, 'malad', 'True' union all
select 2, 2, 'bandra', 'False' union all
select 2, 3, 'Mahim', 'False'

declare @Exam_answer table
(
Paperid int,
Qid int,
Optionno int,
userid varchar(10),
correct varchar(10),
ISattempt varchar(10)
)
insert into @Exam_answer
select 1, 1, 2, 'User1', 'False', 'True' union all
select 1, 2, 1, 'User1', 'True', 'True' union all
select 1, 1, 1, 'User2', 'True', 'True' union all
select 1, 2, 2, 'User2', 'False', 'True' union all
select 1, 1, 1, 'User3', 'False', 'False' union all
select 1, 2, 2, 'User3', 'False', 'True'

select e.userid, q.Question, o.[Option], e.ISattempt, e.correct, a.[Option]
from @Exam_answer e
inner join @Question q on e.Qid = q.Qid
inner join @Question_option o on e.Qid = o.Qid
and e.Optionno = o.Optionno
inner join @Question_option a on e.Qid = a.Qid
and a.Iscorrect = 'True'
-- unmask the below accordingly
-- where e.correct = 'True'
-- where e.correct = 'False'
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources