CODEWITHSUNDEEP
sqldatepivottranspose
dbreienrk1
dbreienrk1

Reputation: 63

SQL Pivot Dates

I am having trouble transposing this data. I have a start and a finish date, but need to account for the total number of resources in use per day. I'd like to pivot the data so that it can account for each day of the project and the number of resources required. I've posted what the source data looks like. I think I need to use a cursor to parse the data out, but I'm not sure how to account for each individual day between the start and finish. Any help would be greatly appreciated.

Source Data:

Project  Start     Finish      ResourceA  ResourceB
1000     1/1/2018  1/5/2018    10         5
1001     2/1/2018  2/1/2018    3          2

Desired Result:

Project  Date      ResourceA ResourceB
1000     1/1/2018  10        5
1000     1/2/2018  10        5
1000     1/3/2018  10        5
1000     1/4/2018  10        5
1000     1/5/2018  10        5
1001     2/1/2018  3         2

Upvotes: 1

Views: 37

Answers (2)

Greg Viers
Greg Viers

Reputation: 3523

in t-sql, sys.all_objects can be used to generate abritrarily large series for cross joining:

select project, dateadd(dd,n-1,start) as date, resourceA, resourceB
 from source_data
inner join 
 (select ROW_NUMBER()  OVER (ORDER BY [object_id]) as n
  FROM sys.all_objects ) numbers on datediff(dd,start,finish)>=n-1

If not using T-SQL, just use any other large table instead.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

First create a calendar table. One with every date you could possibly ever need.

SELECT
  yourTable.Project,
  calendar.CalendarDate,
  yourTable.ResourceA,
  yourTable.ResourceB
FROM
  yourTable
INNER JOIN
  calendar
    ON calendar.CalendarDate BETWEEN yourTable.Start and yourTable.Finish

Alternatively, with a Numbers table... (Using SQL Server syntax for Date functions.)

SELECT
  yourTable.Project,
  DATEADD(day, numbers.id, yourTable.Start),
  yourTable.ResourceA,
  yourTable.ResourceB
FROM
  yourTable
INNER JOIN
  numbers
    ON  numbers.id >= 0
    AND numbers.id <= DATEDIFF(day, yourTable.Start, yourTable.Finish)

Upvotes: 1

Related Questions