Home > Enterprise >  SQL - Splitting a row with week range into multiple rows
SQL - Splitting a row with week range into multiple rows

Time:10-07

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

ID Year StartWeek EndWeek AllocationPercent
5 2021 34 35 50
6 2021 1 3 5

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

ID Year StartWeek EndWeek AllocationPercent
5 2021 34 34 50
5 2021 35 35 50
6 2021 1 1 5
6 2021 2 2 5
6 2021 3 3 5

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:

ID Year StartWeek EndWeek AllocationPercent
5 2021 34 34 50
5 2021 35 35 50
6 2021 1 1 5
6 2021 2 2 5
6 2021 3 3 5

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

  • Related