Concatenating Row Values into 1 Record/String

[yogarajan]

hi friends

i have 3 table

table 1 - > question master
field qid (primary key), question (nvarchar) type (1- single answer, 2-multiple answer)
qid - question - type
1 - 5+3 - 1
2 - 9+1 - 1
3 - 11x11 - 1
4 - ques1 - 2

table 2 multiplechoicea nswer
field MCid (primary key) , qid, answers
MCid - qid - answers
1 - 1 - 10
2 - 1 - 9
3 - 1 - 8
4 - 1 - none
5 - 2 - 10
6 - 2 - 11
7 - 2 - 12
8 - 2 - 13
(each question have 4 choice)

table 3 correctanswer
field cid(p) qid mcid
cid - qid - mcid
1 - 1 - 3
2 - 2 - 5
3 - 3 - 1
4 - 3 - 3
5 - 10 - 1
6 - 10 - 2
7 - 10 - 3

(some question answer more than 1 choice also applicable)


i want this type output format
question - multiple_choice _answer - correct_answer
5+3 - (a)10 (b)9 (c)8 (d)none - 8
9+1 - (a)10 (b)11 (c) 12 (d) 13 - 10
question - (a)ans1 (b)ans2 (c)ans3 (d) ans4 - ans1- ans2- ans3 (multiple answer)

pls help

[ck9663]

Convert it to a delimited string. Read this, then process how it would look in the front-end.

Good luck

--- CK

[yogarajan]

one doubt

hi

thanks for ur reply it is useful
i have one doubt

this is my code


declare @DelimitedStrin g varchar(5000)
select tblquestion.que stionname,(SELE CT @DelimitedStrin g = COALESCE(@Delim itedString+',' , '') + answers FROM tblanswer where tblanswer.quest ionid=que.quest ionid) as answers from tblquestion que

here i got one error mess

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.

my sql statement is correct?
it not pls guide me

thanks

[ck9663]

try:

declare @DelimitedString varchar(5000)
SELECT @DelimitedString = COALESCE(@DelimitedString+',' , '') + answers FROM tblanswer a
inner join questionmaster m on m.qid = 1 and m.qid = a.qid 

print @DelimitedString

Happy Coding!!

-- CK

[yogarajan]

thanks for ur reply
here your using m.qid = 1 know
so my result based on question id =1 but i want all (ie) i want all question id

pls help

[ck9663]

Remove that filter

--- CK