CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2019
SomekindaRazzmatazz
SomekindaRazzmatazz

Reputation: 61

Use relative column name value to calculate DATE value in SQL

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.

Upvotes: 1

Views: 146

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 5

Related Questions