question

poornima.narayanan avatar image
poornima.narayanan asked

Unpivoting 2 columns

This is my sample table: CREATE TABLE TestSample ( Id INT , StudyStartDate DATETIME , StudyEndDate DATETIME , DesignStartDate DATETIME , DesignEndDate DATETIME , StudyStartDiff INT , StudyEndDiff INT , DesignStartDiff INT , DesignEndDiff INT ) INSERT INTO TestSample VALUES ( 1, '2010-04-22', '2012-01-17', '2012-04-09', '2012-10-08', 635, 635, 182, 182 ) INSERT INTO TestSample VALUES ( 2, '2014-04-15', '2015-04-30', '2015-03-30', '2016-04-11', 380, 380, 378, 378 ) SELECT * FROM TestSample I need to unpivot this table to bring StudyStartDate and StudyStartDatediff under a single Milestone code and similarly the other columns So my output should look like : ID Mcode Mdate Variance 1 XY105 2010-04-22 635 1 XY110 2012-01-17 635 1 XY205 2012-04-09 182 1 XY210 2012-10-08 182 2 XY105 2014-04-15 380 2 XY110 2015-04-30 380 2 XY205 2015-03-30 378 2 XY210 2016-04-11 378 **so logic behind is For MCode XY105 : The Mdate should be StudyStartDate & Variance :StudyStartDiff For MCode XY110 : The Mdate should be StudyEndDate & Variance :StudyEndDiff For MCode XY205 : The Mdate should be DesignStartDate & Variance :DesignStartDiff For MCode XY110 : The Mdate should be DesignEndDate & Variance :DesignEndDiff** Can anyone help me with this query ?
sql2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
poornima.narayanan avatar image
poornima.narayanan answered
select id,Dates1,Value1 from( select Id, StudyStartDate,StudyStartDiff, StudyEndDate, StudyEndDiff, DesignStartDate,DesignStartDiff, DesignEndDate ,DesignEndDiff from TestSample ) as Ts unpivot (Dates1 FOR Dates IN ( StudyStartDate,StudyEndDate, DesignStartDate,DesignEndDate) ) AS mrks unpivot (Value1 FOR Value IN ( StudyStartDiff,StudyEndDiff,DesignStartDiff,DesignEndDiff) ) AS Value WHERE Left(Value,10) = Left(Dates,10)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.