Here is an option without using UNPIVOT:
Declare @pivotedData Table (id int, cohort int, year0 int, year1 int, year2 int, year3 int, year4 int, year5 int);
Insert Into @pivotedData (id, cohort, year0, year1, year2, year3, year4, year5)
Values (1, 2010, 0, 0, 0, 1, 0, 0)
, (2, 2011, 0, 0, 0, 0, 1, 0)
, (3, 2010, 1, 0, 0, 0, 1, 0)
, (4, 2011, 0, 1, 0, 0, 1, 0)
, (5, 2010, 0, 0, 1, 0, 1, 0)
, (6, 2011, 0, 0, 0, 0, 1, 0)
, (7, 2010, 0, 0, 1, 0, 1, 0);
Select id
, pd.cohort
, y.yearValue
From @pivotedData pd
Cross Apply (Values (pd.year0)
, (pd.year1)
, (pd.year2)
, (pd.year3)
, (pd.year4)
, (pd.year5)
) As y(yearValue);
I am not sure why you would not use UNPIVOT though:
Select upvt.id
, upvt.cohort
, upvt.yearValue
From @pivotedData pd
Unpivot (yearValue For yearCol In (year0, year1, year2, year3, year4, year5)) upvt;