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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-10 : 08:22:17
|
| Rams writes "i have a table in the table we have 5 coloums,1.userId,cotestId,questionId,Question,Answerfor one contest there is 5 questions.so when one user is submiting his record it will be inserting 5 records.i want output as follows.userid | contestID | question1 | answer1 | Question2 | answer2|Question3 | answer3 | Question4 | answer4 | Question5 | answer5how can i write the queryRegards,Ram" |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-06-10 : 08:56:28
|
Pivot/Crosstab - CASE statements (lookup on BOL). It'll look something like this.SELECT userId , contestId , MAX(CASE WHEN questionId = 1 THEN Question ELSE null END) as 'Question1' , MAX(CASE WHEN questionId = 1 THEN Answer ELSE null END) as 'Answer1' , MAX(...FROM myTableWHERE contestId = GROUP BY userId, contestId If it is more dynamic Search for "Dynamic Crosstab" on this site, or use reporting tool to pivot your data. |
 |
|
|
rams
Starting Member
2 Posts |
Posted - 2004-06-11 : 04:18:14
|
| Hi drymchaser,Thanks Excellent help.Regards,Ram |
|
|
|
rams
Starting Member
2 Posts |
Posted - 2004-06-11 : 04:57:29
|
Hi,Thanks for your help.Regards,Ramquote:
Originally posted by drymchaser
Pivot/Crosstab - CASE statements (lookup on BOL). It'll look something like this.SELECT userId , contestId , MAX(CASE WHEN questionId = 1 THEN Question ELSE null END) as 'Question1' , MAX(CASE WHEN questionId = 1 THEN Answer ELSE null END) as 'Answer1' , MAX(...FROM myTableWHERE contestId = GROUP BY userId, contestId If it is more dynamic Search for "Dynamic Crosstab" on this site, or use reporting tool to pivot your data.
|
|
|
|
|
|
|
|
|
Sql doubt