Columns as values/Un-pivot table in SQL-CodePudding

I have the following table.

Table_1

ID	12/1	1/1	2/1
X	1	2	3
Y	4	5	6
Z	7	8	9

I want the following table,

ID	Date	Forecast
X	12/1	1
X	1/1	2
X	2/1	3
Y	12/1	4
Y	1/1	5
Y	2/1	6
Z	12/1	7
Z	1/1	8
Z	2/1	9

Is there anyway I can do this in SQL?

Any help will be appreciated!

Thanks in advance.

CodePudding user response：

UNPIVOT or the VALUES approach would be more performant, BUT based on your column names, I suspect you will have variable/expanding columns over time.

Here is an approach that will dynamically unpivot your data without actually using Dynamic SQL or having to specify the columns (only the ones to exclude)

Example

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

Results

ID  Date    Forecast
X   12/1    1
X   1/1     2
X   2/1     3
Y   12/1    4
Y   1/1     5
Y   2/1     6
Z   12/1    7
Z   1/1     8
Z   2/1     9