-- Creo una funcion de usuario que nos ayudara a realizar el split de los registros
CREATE FUNCTION NumbersTable (
@fromNumber int,
@toNumber int,
@byStep int
)
RETURNS @NumbersTable TABLE (i int)
AS
BEGIN
WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
UNION ALL
SELECT i + @byStep
FROM CTE_NumbersTable
WHERE
(i + @byStep) <= @toNumber
)
INSERT INTO @NumbersTable
SELECT i FROM CTE_NumbersTable OPTION (MAXRECURSION 0)
RETURN;
END
-- Creo mi tabla de prueba
Create table #repeatrows (PartNumber varchar(30), PartsSkid int, Quantity int, RowsNummber int)
-- Inserto los registros que publicaste
insert into #repeatrows values('NS041.1P', 12, 42, 4),
('NS041.1P', 12, 183, 16),
('NS041.1P', 12, 387, 33),
('NS041.1P', 12, 138 ,12)
-- Pruebo la funcion
SELECT * -- PartNumber, PartsSkid, Quantity, RowsNumber
FROM #repeatrows r
CROSS APPLY dbo.NumbersTable (1,r.RowsNummber,1) --n
ORDER BY PartNumber