Use relative column name value to calculate DATE value in SQL

Question

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.

I suggest you use a calendar table. This is a table with one record per day. There are many online examples of how to create. Then you join your table to this table on month/year. This for example turns one row into 28 rows for Feb. Then you use case statements to pivot — Nick.Mc, Dec 12 '22 at 23:20
The source of the month value will be within a separate file that will be processed alongside the specific table. The table itself is coming from a CSV file. — SomekindaRazzmatazz, Dec 12 '22 at 23:22
Seems like you'll going to need a dynamic unpivot here, if I am honest. If you could fix the source data, that would very likely be the *far* better option. — Thom A, Dec 12 '22 at 23:33

score 5 · Accepted Answer · answered Dec 12 '22 at 23:33

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

That's a pretty neat trick with JSON, John.. Thanks for posting it! — Jeff Moden, Dec 14 '22 at 03:32

Use relative column name value to calculate DATE value in SQL

1 Answers1