Loading

Subquery Question

Subquery Question

Arshad Ali

Arshad Ali

6y785Learn iOS Programming

Hello folks,

i have the two table 1 is tblquestion and 2 is tblQuestionOption so i want below output so how can i do?

tblQuestion

QID	Name	Mark
1	ABC	2.0
2	PQR	2.0
3	XYZ	2.0

tblQuestionOption

QOID	QID	Option
1	1	A
2	1	B
3	1	C
4	1	D
5	2	A
6	2	B
7	2	C
8	3	A
9	3	D

I want to that O/p using SQL server query.

ID	Question	Marks
1	ABC? Options : A. B. C D	2.0
2	PQR A. B. C.	2.0
3	A. D.	2.0

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Accepted answer
Hey,

Please try below Query which will return last column as comma separated values which can loop through in you presentation layer. I mean if you are using MVC then you can do in your view or you are using .net then aspx page.
SELECT  QID,Name,Mark
       ,STUFF((SELECT ', ' + CAST(Options AS VARCHAR(10)) [text()]
         FROM tblQuestionOption
         WHERE QID = t.QID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') Options
FROM tblquestion t
GROUP BY QID,Name,Mark