title: Converting columns to row in SQL Server
body: <p>My problem: I have my base table that stores username, and their feedback response. In the table with each user I have 2 questions and 2 responses. </p>
  
  <p><code>ans1 ans2</code> are text columns where user can entry their comments against each question/answer pair.</p>
  
  <p>Input table:</p>
  
  <pre><code>username    question1   question2   opt1    ans1    opt2    ans2
  -----------------------------------------------------------------------
  user1   ques1   ques2   a   answer1 b   answer2
  user2   ques1   ques2   c   answer11    d   answer21
  </code></pre>
  
  <p>I want my output (for the given input as above table)query should produce a result in below format:</p>
  
  <pre><code>Username    Question    Option  Comment
  ----------------------------------------------------
  user1   ques1   a   answer1
  user1   ques2   b   answer2
  user2   ques1   c   answer11
  user2   ques2   d   answer21
  </code></pre>
  
  <p>I tried this query to get the result but it is somehow taking all possible combinations..pls suggest..</p>
  
  <pre><code>SELECT Username,Question,Answers,Options
  FROM 
  (SELECT username,opt1,opt2,ans1,ans2,ques1,ques2  
  FROM dbo.tab1) p
      UNPIVOT(Question FOR q1 IN (ques1, ques2))AS unpvt1
      UNPIVOT(Answers FOR answer1  IN (ans1, ans2))AS unpvt2
      UNPIVOT(Options FOR a  IN (opt1, opt2))AS unpvt3
  GO
  </code></pre>