How to convert data rows to columns ?

Question

I have a table tblUser , there is userId,firstName,LastName,Mobile,.......,QuestionID. Another table it's name tblResults There is questionID,Question,choiceID,choice,.........

EG:

tblUser

userID - FirstName- LstName -Mobile ... ... ... - QuestionID - ChiceID

001 - xx - yy - 03212 - 01 01
001 - xx - yy - 03212 - 02 02
002 - xxx - yyy - 03425 - 01 02
002 - xxx - yyy - 03425 - 02 01
003 - xxxx - yyyy - 03429 - 03 02

003 - xxxx - yyyy - 03429 - 03 01

tblResults
---------------------------------------------------------------------
QuestionID Question ChoiceID Chice .... ....

---------------------------------------------------------------------
01 - Are you - 01 - Male
01 - Areyou - 02 - Female
02 - From - 01 - xxxxx
02 - FROM - 02 - yyyyy

---------------------------------------------------------------------

I want to get result shown in following table
-------------------------------------------------------------------------------
UserID FirstName LastName Mobile Areyou From
-------------------------------------------------------------------------------
001 - xx - yy - 03212 - Male - yyyyy
002 - xxx - yyy - 03425 - Female - xxxxx
003 - xxxx - yyyy - 03429 - Female - xxxxx

Answer 1

You get the unique users with their respective choises for each question, and join in the tblResults table once for each question:

select
  u.userID, u.FirstName, u.LastName, u.Mobile,
  q1.Choice as Areyou,
  q2.Choice as [From]
from (
  select userID, FirstName, LastName, Mobile,
  sum(case QuestionId when '01' then ChoiseId else 0 end) as ChoiseId1,
  sum(case QuestionId when '02' then ChoiseId else 0 end) as ChoiseId2
  from tblUser
) as u
inner join tblResults as q1 on q1.QuestionID = '01' and q1.ChoiseID = u.ChoiseId1
inner join tblResults as q2 on q2.QuestionID = '02' and q2.ChoiseID = u.ChoiseId2

Answer 2

baically, we can create a table t (UserID, ChoiceID) for each question, then inner join all these tables. then we got (UserID, ChoiceID_for_Question_1, ChoiceID_for_Question_2, ...). add the userinfomation back, we got the desirable result.

select t1.UserID, t1.FirstName, t1.LastName, t1.Mobile, t1.Choice, t2.Choice
from 
  (select u.UserID, u.FirstName, u.LastName, u.Mobile, r.Choice
  from tblUser u, tblResults r 
  where u.questionID = '01' and u.QuestionID=r.QuestionID and u.ChoiceID = r.ChoiceID) t1
inner join 
  (select u.UserID UserID, r.Choice
  from tblUser u, tblResults r 
  where u.questionID = '02' and u.QuestionID=r.QuestionID and u.ChoiceID = r.ChoiceID) t2
/* 
inner join
  (...) t3
*/
on t1.UserID = t2.UserID /* and t2.UserID = t3.UserID */

Answer 3

Am i right if you want to pivot your results? http://technet.microsoft.com/en-us/library/ms177410.aspx

here's a good example of how you can do this.

Regards