Search code examples
sqlsql-serversql-server-2014unpivot

Unpivot specific columns into 2 output columns

I have the following table source table A:

  [start1]     [end1]       [start2]     [end2]       [start3]      [end3]
  2019-01-01   2019-01-26   2019-01-27   2019-02-23   2019-02-24    2019-03-30

How can I UNPIVOT to get the result (ideally with an index): 

[index]  [start]     [end]       
1        2019-01-01  2019-01-26
2        2019-01-27  2019-02-23
3        2019-02-24  2019-03-30
Solution

  • You want apply :

    select row_number() over (order by tt.start) as [index], tt.*
from table t cross apply 
     ( values ([start1], [end1]), ([start2], [end2]), . . .
     ) tt (start, end);