I have the following table structure and data in the database table:

I need to split the multi-week rows into multiple single-week rows, and the end result should be:

Any help with this would be highly appreciated! There are a lot of threads regarding splitting date ranges into multiple rows but I cannot seem to modify those to fit my use case. I know that most likely I need a tally table with the week numbers (which I already have).

CodePudding user response：

Another way to think about this is, because we know the max weeknumber is 53, to generate the set of all possible week numbers, then outer join to that set each week in any source row that is within that range.

;WITH n(n) AS 
(
  SELECT 0 UNION ALL SELECT n 1 FROM n WHERE n <= 53
)
SELECT w.ID, 
       w.Year, 
       StartWeek = n.n, 
       EndWeek = n.n, 
       w.AllocationPercent
  FROM n
  INNER JOIN dbo.TableName AS w
  ON n.n BETWEEN w.StartWeek AND w.EndWeek
  ORDER BY w.ID, w.Year, n.n;

Results:

• Example db<>fiddle

CodePudding user response：

You can use recursive cte :

;with cte as (
      select t.id, t.year, t.startweek, t.endweek, t.AllocationPercent
      from t
      union all
      select id, year, startweek   1, endweek, AllocationPercent
      from cte c
      where startweek < endweek
)
select id, year, startweek, startweek as endweek, AllocationPercent
from cte
order by id, startweek, endweek;

db fiddle