How to create multiple rows from a single row in snowflake using sql

Operating on string (YYYY-Wx) is not recommended, and it will be much easier if input is provided as first day of week (YYYY-MM-DD) and only transformed to YYYY-Wx for presentation.

Key ideas of script below:

• transform week to date - column yw (DATEADD)
• find next date (LEAD)
• compute week difference (DATEDIFF)
• create tally table (VALUES (0), (1),(2),(3),(4))
• compute new dates
• transform back to YYYY-Wx: (CONCAT)

Sample:

CREATE OR REPLACE TABLE tab(id INT, week TEXT, value INT )
AS
SELECT 1,  '2022-W1', 200 UNION 
SELECT 2,  '2022-W3', 500 UNION
SELECT 2,  '2022-W6', 600;

Query:

WITH cte AS (
  SELECT *, 
    DATEADD('week',REPLACE(week, LEFT(week,6)),
            DATE_FROM_PARTS(LEFT(week,4),1,1)) AS yw,
    DATEDIFF('week', YW, LEAD(yw) OVER(PARTITION BY id ORDER BY yw)) AS diff
  FROM tab
) 
SELECT cte.ID, 
   CONCAT(YEAR(DATEADD('week', s.num, cte.YW)), '-W',
          WEEK(DATEADD('week', s.num, cte.YW))) AS new_week, cte.value
FROM cte
JOIN (VALUES (0), (1),(2),(3),(4)) AS s(num)
  ON  s.num < cte.diff
  OR cte.diff IS NULL
ORDER BY cte.ID, cte.YW;

Output:

Warning: The computation YYYY-WW -> date -> back to week may not work across multiple years i.e, 2022-W52 -> 2023-W1. Testing such scenario is recommended. Ideally storing week as date.