By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

Concatenating Row Values into 1 Record/String

100+
P: 115
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 multiplechoiceanswer
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
Jun 29 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Convert it to a delimited string. Read this, then process how it would look in the front-end.

Good luck

--- CK
Jun 30 '09 #2

100+
P: 115
hi

thanks for ur reply it is useful
i have one doubt

this is my code


declare @DelimitedString varchar(5000)
select tblquestion.questionname,(SELECT @DelimitedString = COALESCE(@DelimitedString+',' , '') + answers FROM tblanswer where tblanswer.questionid=que.questionid) 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
Jun 30 '09 #3

ck9663
Expert 2.5K+
P: 2,878
try:

Expand|Select|Wrap|Line Numbers
  1. declare @DelimitedString varchar(5000)
  2. SELECT @DelimitedString = COALESCE(@DelimitedString+',' , '') + answers FROM tblanswer a
  3. inner join questionmaster m on m.qid = 1 and m.qid = a.qid 
  4.  
  5. print @DelimitedString 
  6.  

Happy Coding!!

-- CK
Jun 30 '09 #4

100+
P: 115
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
Jul 8 '09 #5

ck9663
Expert 2.5K+
P: 2,878
Remove that filter

--- CK
Jul 8 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.