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 ?
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)
18 People are following this question.
