Finding the Questions & Answer list for a User + Student Management System

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 481
--Sample Code for listing out attempted questions & answers list for the User's (Name = Anu UserID = 600)

--Sample Tables and data
declare @InputCategory table(InputID int, Type varchar(30))
insert into @InputCategory
select 1, 'Single select' union all
select 2, 'Multi Select' union all
select 3, 'TextArea'

declare @Question Table(QuestionId int, QName varchar(50), InputID int)
insert into @Question
select 1, 'Quality', 1 union all
select 2, 'Feedback abt supervisor', 2 union all
select 3, 'Your comment', 3


declare @ChoiceMaster Table (ChoiceID int, OptionChoiceName varchar(20))
insert into @ChoiceMaster
select 201, 'Good' union all
select 202, 'Avg' union all
select 203, 'Supportive' union all
select 204, 'Approachable' union all
select 205, 'Caring'

declare @QuestionOption Table(QoptID INT, QId INT, ChoiceID INT)
INSERT INTO @QuestionOption
SELECT 100, 1, 201 union all
SELECT 101, 1, 202 union all
SELECT 102, 2, 203 union all
SELECT 103, 2, 204

declare @User Table(UserId int, UserName varchar(30))
insert into @User
select 600, 'Anu' union all
select 601, 'Binu'

DECLARE @Answer Table(AnsID INT, UserID INT, QoptID INT)
INSERT INTO @Answer
SELECT 1, 600, 101 union all
SELECT 2, 600, 102 union all
SELECT 3, 600, 103 union all
SELECT 4, 601, 100
/*=============
Expected result for user Anu
QID Question Answer
1 Quality avg
2 Supervisorfeedback Supportive,Approachable */

-- Query to get the required output
;With CTE
AS
(
Select
distinct qst.QName as Question,
qst.QuestionId,
och.OptionChoiceName as Answer
From
@Answer ans inner join
@QuestionOption qop on ans.QoptID = qop.QoptID
inner join @Question qst on qop.QId = qst.QuestionId
inner join @ChoiceMaster och on qop.ChoiceID= och.ChoiceID
where
ans.UserId= 600
)
SELECT c.*,
STUFF((SELECT ',' + Answer FROM CTE WHERE Question = c.Question AND QuestionId = c.QuestionId FOR XML PATH('')),1,1,'') AS Answer
FROM (SELECT DISTINCT Question,QuestionId FROM CTE)c

Comments or Responses

Login to post response