Query urgent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query urgent

Author

Topic

chbala85
Starting Member

49 Posts

Posted - 2013-08-07 : 06:57:44

Hi all,

response patientFormId questionId
Considering 1391 10231
Education 1391 10231
Educationcomplete 1391 10231
Existing 1391 10232
Forms given 1391 10232
In Progress 1391 10232
Refuses1 1391 10232
aaa 1392 10231
bbb 1392 10231
Refuses2 1392 10232
aaa 1393 10231
bbb 1393 10231
Refuses3 1393 10232
aaa 1394 10231
bbb 1394 10231
--- --- -----

i above table patientformid automatically Incremented when new data added and questionid also diffeent will add some times.

my requeirement is :

patientforid and questionid same then add respose as single field data.
for ex:

respose patientformid questionid

Considering,Education,Educationcomplete 1391 10231

Please help me urgent.........

Thanks,
Krsh.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 07:26:07

--Method1:

SELECT patientFormId, questionId
	,STUFF((SELECT ',' + s.response FROM TabNAme s WHERE s.patientFormId= t.patientFormId AND s.questionId = t.questionId FOR XML PATH('')),1,1,'') AS Responses
FROM TabNAme t
GROUP BY patientFormId, questionId

--Method2:
SELECT patientFormId, questionId, MAX(s.Responses)
FROM TabNAme t
CROSS APPLY (SELECT ','+Response 
		FROM TabNAme
		WHERE patientFormId= t.patientFormId AND questionId = t.questionId
		FOR XML PATH('')
	     ) s (Responses)
GROUP BY patientFormId, questionId

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 07:53:04

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chbala85
Starting Member

49 Posts

Posted - 2013-08-07 : 09:32:01

thank you.........

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-08 : 01:18:43