CODEWITHSUNDEEP
sqlsql-servert-sql
Ville Alijärvi
Ville Alijärvi

Reputation: 3

SQL - Splitting a row with week range into multiple rows

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).

Upvotes: 0

Views: 379

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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

Upvotes: 1

Related Questions