Convert multiple rows into multiple column

Hi Punnet,

        

              I have tried this,will u help me more a single step ,so that ,i can acheived an answer.

            

              select Qid,Ans1,Ans2,Ans3,Ans4
from
(
select Qid, Answer,
'Option' + CAST(ROW_NUMBER()over (Partition by Qid order by Qid)as varchar(10)) as columnname
from Answer) d
pivot
(
max(Answer)
for columnname in (Ans1,Ans2,Ans3,Ans4)
) piv;

 

 I am getting result like as below.

 

Qid    Ans1   Ans2  Ans3   Ans4

1        Null      Null    Null      Null

2        Null     Null    Null      Null

 

 

 My actual result should be like this.

  2        5          9       6           4     

 

my database structure is as below.

 

Qid    Ans   Option

 1        8       A

 1        7       B

 1        5       C

 1        6       D

 2        5       A

 2        9       B

 2        6       C

 2        4       D

 

Plz. Suggest me what i am doing wrong.

Please refer to this link

 

https://forums.asp.net/t/1967025.aspx?how+to+display+row+values+in+columns+in+sql+server+2008