Use relative column name value to calculate DATE value in SQL-CodePudding

I have a table that has some peculiar data arrangements where there are 28-31 columns corresponding with the day of the month for a series of unique IDs. What I'm trying to do is bring that into a more feasible format with actual date values. My tables look something such as below:

DECLARE @Month VARCHAR(3) 
SET @Month = 'NOV'

ID	Status	1	2	3	4	5
111	Active	A	2	3	4	Z
222	Inactive	Z	5	f	6	7

I'd like ideally to have a way to convert this into something like the below:

ID	Status	Date	Value
111	Active	11/1/2022	A
111	Active	11/2/2022	2
111	Active	11/3/2022	3
111	Active	11/4/2022	4
111	Active	11/5/2022	Z
222	Inactive	11/1/2022	Z
222	Inactive	11/2/2022	5
222	Inactive	11/3/2022	f
222	Inactive	11/4/2022	6
222	Inactive	11/5/2022	7

An approach that is flexible as to the number of columns, with respect to different number of days in the month, would be preferable with some minor considerations to performance.

CodePudding user response：

One option is to use JSON to "dynamically" unpivot your data

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

Results

ID  Status  Date        Value
111 Active  2022-11-01  A
111 Active  2022-11-02  2
111 Active  2022-11-03  3
111 Active  2022-11-04  4
111 Active  2022-11-05  Z