CODEWITHSUNDEEP
sqlsql-servert-sql
Karina1117
Karina1117

Reputation: 69

Reshape table wide to long

I'm trying to reshape a wide data table to long. My current code works but I think it's inefficient and wonder if there's a better way to do this. The original table looks like this:

Location    Date1     Date2      Date3       .....  Date 80
   A      1-Jan-15   3-Mar-15   7-Apr-15            4-Apr-16
   B      3-Jan-15   5-Mar-15   6-Apr-15            3-Apr-16
   c      2-Jan-15   7-Mar-15   8-Apr-15            2-Apr-16

And I want to reshape it like this:

Location    Date
A          1-Jan-15
A          3-Mar-15
A          7-Apr-15
.
.
A          4-Apr-16
B          3-Jan-15
...

This is the code I used but since there are 80 date variables, I found it inefficient to list all 80 values in the cross apply clause. Is there a better way to get the same result?

select t.Location, Date
from my_table t
cross apply 
( 
  values (1, Date1),
         (2, Date2),
         (3, Date3),
            ...
         (80, Date80)
) x (Location, Date);

Upvotes: 1

Views: 440

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Here is an option that will dynamically unpivot your data with using dynamic sql

Example

Select A.Location
      ,B.*
 From  YourTable A
 Cross Apply (
                Select [Key]
                      ,Value
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                 Where [Key] not in ('Location')
             ) B

Returns

enter image description here

Upvotes: 2

Related Questions