SQL Server - Need to 'Pivot' a date range into separate records

Question

I have a large table of records that records consultants work. I need to take each row of data that includes a date range and transform the row into separate records in a new table. I have used one record as an example. See below (dates in UK format dd/mm/yyyy) :

C_Name Customer_Name StartDate EndDate

Bob Jones AcmeHealth 1/2/2018 5/2/2018

Transform into …

Name Customer_Name WorkDate

Bob Jones AcmeHealth 1/2/2018
Bob Jones AcmeHealth 2/2/2018
Bob Jones AcmeHealth 3/2/2018
Bob Jones AcmeHealth 4/2/2018
Bob Jones AcmeHealth 5/2/2018

Any ideas how I can do this ?

If you find yourself saying you need to take `each row` and transform it, you're almost certainly going to be working with the `APPLY` operator or a cursor. — Adam, Nov 29 '18 at 13:43

John Cappelletti · Answer 1 · 2018-11-29T13:41:02.547

If you don't have a calendar or tally table, you can use CROSS APPLY in concert with an ad-hoc tally table.

Example

Select A.C_Name
      ,A.Customer_Name
      ,WorkDate = B.D
 From  YourTable A
 Cross Apply (
                Select Top (DateDiff(DAY,StartDate,EndDate)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),StartDate) 
                 From  master..spt_values n1,master..spt_values n2
             ) B

Returns

C_Name      Customer_Name   WorkDate
Bob Jones   AcmeHealth      2018-02-01
Bob Jones   AcmeHealth      2018-02-02
Bob Jones   AcmeHealth      2018-02-03
Bob Jones   AcmeHealth      2018-02-04
Bob Jones   AcmeHealth      2018-02-05

If you really want the dates formated (which belongs in the presentation layer), you can make the following change:

...
,WorkDate = convert(varchar(10),B.D,101)
...

John, thanks very much for replying. Thats worked a treat. Much appreciated, MoonMoth — MoonMoth, Nov 29 '18 at 15:53

SQL Server - Need to 'Pivot' a date range into separate records

1 Answers1