Convert multiple rows into multiple column

Question

My table structure is like this Option Ans A 8 B 10 C 12 D 14 I want to display this in this format A) 8 B) 10 C) 12 D) 14 Plz suggest mehow i can achieve this.

Puneet Kankar · Answer

try this code select * from ( select Qid AnsOptionA from tempdata ) d pivot ( max(Ans) for OptionA in (A B C D) ) piv; put table name in place tempdata in above code.

Puneet Kankar · Answer

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

priyanka · Answer

Hi Punnet I have tried thiswill u help me more a single step so that i can acheived an answer. select QidAns1Ans2Ans3Ans4 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 (Ans1Ans2Ans3Ans4) ) 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. Qid Ans1 Ans2 Ans3 Ans4 1 8 7 5 6 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.