SQL中如何根据FromYM与ToYM的年月差值拆分数据为多行显示

我有如下数据表：

EmpID   FromYM  ToYM    EmpYl      EmpX1 
-----    ------  -----  ------     ----------
1001    202101  202101  20210103   20210103
1001    202102  202103  20210103   20210103

我需要基于上述数据按规则拆分：如果FromYm（代表年月）和ToYM（代表年月）的差值为2，则拆分生成2行数据。

预期结果示例：

EmpID   FromYM  ToYM    EmpYl     DiffNoCount
------  ------  ----    -----     ------
1001    202101  202101  20210103  1 
1001    202102  202103  20210103  1 
1001    202102  202103  20210103  2

已尝试代码 #

IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
DROP TABLE #rawdata1
END
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
DROP TABLE #rawdata2
END
GO

DECLARE @Max AS INT
DECLARE @Kount AS INT
SET @Kount = 1
SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS row,EmpID
,FromYM
,ToYM
, EmpYl
,EmpX1
INTO #rawdata1
FROM [dbo].[ASAAValue1]
ORDER BY EmpID
SET @Max = (SELECT MAX(FromYM) FROM #rawdata1)

CREATE TABLE #Rawdata2
(
[Row] INT,
Rolling INT,
RollingAvg DECIMAL(15,2),
RollingFinal INT
)
WHILE (@kount < @max)
BEGIN
INSERT INTO #rawdata2
SELECT @Kount AS Row , FromYM AS Rolling
, ToYM AS RollingAvg,
   CASE WHEN  CONVERT(INT,CONVERT(NVARCHAR(6),EmpYl))>=FromYM
    THEN  
     FromYM   
    ELSE
      FromYM+1  
     END 
 
FROM #rawdata1
WHERE row BETWEEN @Kount - 12  AND @Kount 
SET @Kount = @Kount + 1
END
SELECT rd1.row,
rd1.EmpID,Rd1.FromYM,Rd1.ToYM,Rd1.EmpYl, rd2.RollingFinal AS Final FROM #rawdata2 rd2
INNER JOIN #rawdata1 rd1
ON rd1.row = rd2.row

实现方案 #

使用递归CTE可以更简洁高效实现需求，无需复杂的临时表循环逻辑：

WITH SplitCTE AS (
    -- 取所有原始行作为第一行，序号默认1
    SELECT EmpID, FromYM, ToYM, EmpYl, 1 AS DiffNoCount
    FROM [dbo].[ASAAValue1]
    UNION ALL
    -- 递归生成后续行，直到序号等于区间总月数
    SELECT EmpID, FromYM, ToYM, EmpYl, DiffNoCount + 1
    FROM SplitCTE
    WHERE DiffNoCount < (ToYM - FromYM + 1)
)
SELECT EmpID, FromYM, ToYM, EmpYl, DiffNoCount
FROM SplitCTE
ORDER BY EmpID, FromYM, DiffNoCount
-- 如果需要处理超过100个月的长区间，加上下面的配置解除递归限制
-- OPTION (MAXRECURSION 0)

逻辑说明：

• 单月区间ToYM-FromYM+1结果为1，仅保留1行
• 跨2月区间ToYM-FromYM+1结果为2，自动拆分2行并生成1、2的序号

内容的提问来源于stack exchange，提问作者Venkat