UNPIVOT: Columns to rows

January 26, 2012

Q : Write a query to format the columns of a table to rows?
A : Lets say there is a table 

DECLARE @t TABLE (
    SquadID INT, 
    Date DATETIME, 
    Q01 VARCHAR(10), 
    Q02 VARCHAR(10), 
    Q03 VARCHAR(10), 
    A01 VARCHAR(10), 
    A02 VARCHAR(10),     
    A03 VARCHAR(10))


INSERT INTO @t(SquadID, Date,  Q01, Q02, Q03, A01, A02, A03) SELECT 123,'2008-09-19','5.1','2.1','3.0','DEG','ABC','CDE'
I want a query to display results as below
Q1 A1
Q2 A2
Q3 A3


This is not straight forward we can use UNPIVOT to achieve this to some extend.  And, where clause does the trick. I found this on Internet. 


select ROW_NUMBER() OVER(ORDER BY Question_ID), Question, Answer FROM 
(SELECT Q01,Q02,Q03,A01,A02,A03 FROM @t) p
UNPIVOT
   (Question FOR Question_ID IN 
      (Q01,Q02,Q03) 
)AS up
UNPIVOT
   ( Answer FOR Answer_ID IN 
      (A01,A02,A03)
)AS an
where RIGHT(Answer_ID,1)=RIGHT(Question_ID,1);

You Might Also Like

0 comments

Popular Posts

Twitter