CODEWITHSUNDEEP
sqlsql-serversql-server-2014unpivot
Antonio
Antonio

Reputation: 81

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

Upvotes: 1

Views: 46

Answers (2)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

Please try this:

SELECT p.[index],p.[start],p.[end]
FROM (
    SELECT TRY_CONVERT(INT,REPLACE(REPLACE(up.Param,'start',''),'end','')) AS [index]
        ,CASE WHEN up.Param LIKE 'start%' THEN 'start' ELSE 'end' END AS [Type]
        ,up.[Value]
    FROM [YourTableName] t
    UNPIVOT(Value FOR Param IN ([start1],[end1],[start2],[end2],[start3],[end3])) up
) a
PIVOT(MAX(a.[Value]) FOR [Type] IN ([start],[end])) p
;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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);

Upvotes: 3

Related Questions