--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