Survey results combined

[beakerman29]

I have two tables first table called userinfo has a field called surveynum and surveyid. the second table has fields such as questionid,questionresult,questioncomment, and surveynum. the two tables are joined by the surveynum field which helps to track back which user and what survey the info is for. Now for the Million dollar question. I need to have a SQL query that will take combine the survey results into one row like such

userid,surveynumber,questionresponse1 ...,questioncomment1...,questionresponse2,question comment2...

in other words I want to take all the responses from one user for a particular survey and group them into one table. I tried group by and union with no luck. I am sure i am just overlooking the obvious.

Any help would be appreciated


I realize i could do this via coding but i would also like to make it a view.

[azimmer]

I have two tables first table called userinfo has a field called surveynum and surveyid. the second table has fields such as questionid,questionresult,questioncomment, and surveynum. the two tables are joined by the surveynum field which helps to track back which user and what survey the info is for. Now for the Million dollar question. I need to have a SQL query that will take combine the survey results into one row like such

userid,surveynumber,questionresponse1 ...,questioncomment1...,questionresponse2,question comment2...

in other words I want to take all the responses from one user for a particular survey and group them into one table. I tried group by and union with no luck. I am sure i am just overlooking the obvious.

Any help would be appreciated


I realize i could do this via coding but i would also like to make it a view.

In SQL Server 2005 there's a PIVOT operator you can use. Read this (even if you use 2000): http://www.databasejournal.com/features/mssql/article.php/3521101

Here's another set for 2000 (works with 2005 but not ideal): http://www.mssqltips.com/tip.asp?tip=937

[beakerman29]

my data looks like such

38,1,1,"comment"
38,2,1,"comment"
38,3,2,"no comment"

i would like it to be

38,1,"comment",1,"comment",2,"no comment"