CREATE TABLE #Item
(
  TransactionID INT 
, RecordSequenceNumber INT
)

INSERT INTO #Item
SELECT 1,1
UNION SELECT 1,2
UNION SELECT 1,3
UNION SELECT 1,4
UNION SELECT 2,1
UNION SELECT 2,2
UNION SELECT 2,3
UNION SELECT 3,1
UNION SELECT 3,2
UNION SELECT 3,3
UNION SELECT 3,4
UNION SELECT 3,5
UNION SELECT 4,1
UNION SELECT 4,2
UNION SELECT 5,1
UNION SELECT 5,2
UNION SELECT 6,1
UNION SELECT 7,1
UNION SELECT 7,2
UNION SELECT 7,3
UNION SELECT 7,4
UNION SELECT 7,5
UNION SELECT 7,6
UNION SELECT 7,7
UNION SELECT 8,1
UNION SELECT 8,2

SELECT AVG(RecCount) FROM
(
SELECT COUNT(RecordSequenceNumber)RecCount
FROM #Item
GROUP BY TransactionID
)AS T1

-- Sample transaction table
declare @t table 
(
	TransactionID	int,
	SequenceNo		int
)

insert @t
select 1, 1 union all
select 1, 2 union all
select 2, 1 union all
select 2, 2 

-- This is the numer of records needed for each transaction id
declare @needed int 
set @needed = 5

declare @t2 table
(
	TransactionID	int,
	NumberOfSeq		int,
	NumberNeeded	int
)
insert into @t2
select TransactionID, count(SequenceNo), @needed
from @t
group by TransactionID
having count(SequenceNo) < @needed;

WITH ext(TransactionID, NumberOfSeq) AS 
(
	SELECT TransactionID, NumberOfSeq + 1
		FROM @t2 
	UNION ALL
	SELECT ext.TransactionID, ext.NumberOfSeq + 1
		FROM @t2 t
		JOIN ext ON ext.TransactionID = t.TransactionID
	WHERE ext.NumberOfSeq + 1 <= t.NumberNeeded 
) 

INSERT INTO @t
SELECT TransactionID, NumberOfSeq as SequenceNo
FROM ext

SELECT * FROM @t
ORDER BY transactionid

BEGIN TRAN 

	DECLARE @ExtraItemsNeeded INT
	SET @ExtraItemsNeeded = 3

	CREATE TABLE dbo.Item 
	(
	  TransactionID INT 
	, RecordSeqNo INT 
	, ItemDescription VARCHAR(30)
	)
	INSERT INTO dbo.Item(TransactionID, RecordSeqNo, ItemDescription) 
	SELECT 1,1, 'Apples'
	UNION SELECT 1,2, 'Bananas'
	UNION SELECT 1,3, 'Cherrys';
	--UNION SELECT 1,4
	--UNION SELECT 2,1
	--UNION SELECT 2,2
	--UNION SELECT 2,3
	--UNION SELECT 3,1
	--UNION SELECT 3,2
	--UNION SELECT 4,1
	--UNION SELECT 4,2
	--UNION SELECT 4,3
	--UNION SELECT 4,4
	--UNION SELECT 4,5;

-- THIS FORMS THE ANCHOR STATEMENT
	SELECT		  i1.TransactionID
				, i1.RecordSeqNo
				, i2.RecordSeqNo AS MaxRecordSeqNo
				, ItemDescription 
	FROM		dbo.Item i1 
	INNER JOIN (
				SELECT TransactionID, MAX(RecordSeqNo) AS RecordSeqNo
				FROM dbo.Item 
				GROUP BY TransactionID
				)AS i2 
			ON	i2.TransactionID = i1.TransactionID;

-- THE CTE STATEMENT
	WITH CTE_Item(TransactionID, RecordSeqNo, MaxRecordSeqNo, ItemDescription) AS 
	(
	SELECT		  i1.TransactionID
				, i1.RecordSeqNo
				, i2.RecordSeqNo AS MaxRecordSeqNo
				, i1.ItemDescription
				--, 0 AS IterationCount 
	FROM		dbo.Item i1 
	INNER JOIN (
				SELECT TransactionID, MAX(RecordSeqNo) AS RecordSeqNo
				FROM dbo.Item 
				GROUP BY TransactionID
				)AS i2 
			ON	i2.TransactionID = i1.TransactionID
	UNION ALL
-- THIS IS THE MORE DESIRED OUTPUT - BUT HOW DO I DO MORE THAN 1 ITERATION??????
--	SELECT		  c.TransactionID
--				, c.RecordSeqNo + c.MaxRecordSeqNo AS RecordSeqNo
--				, c.MaxRecordSeqNo
--				, c.ItemDescription
--	FROM		dbo.Item i
--	INNER JOIN	CTE_Item c 
--			ON	c.TransactionID = i.TransactionID
--			AND c.RecordSeqNo	= i.RecordSeqNo

-- THIS DOES MULTIPLE ITERATIONS, BUT ONLY ON THE LAST ITEM
	SELECT    TransactionID
			, RecordSeqNo + MaxRecordSeqNo AS RecordSeqNo
			, MaxRecordSeqNo
			, ItemDescription
	FROM CTE_Item
	)
	SELECT * FROM CTE_Item

ROLLBACK TRAN 

BEGIN TRAN 

	DECLARE	  @NumberOfIterations			INT


	SET @NumberOfIterations = 3

	CREATE TABLE dbo.Item 
	(
	  TransactionID INT 
	, RecordSeqNo INT 
	, ItemDescription VARCHAR(30)
	)
	INSERT INTO dbo.Item(TransactionID, RecordSeqNo, ItemDescription) 
	SELECT 1,1, 'Apples'
	UNION SELECT 1,2, 'Bananas'
	UNION SELECT 1,3, 'Cherrys';


WITH CTE_Item (TransactionID, RecordSeqNo, MaxRecordSeqNo, NextRecordSeqNo, ItemDescription, Iteration)
AS
(
	SELECT	  i1.TransactionID
			, i1.RecordSeqNo
			, i2.MaxRecordSeqNo
			, 0 AS NextRecordSeqNo
			, i1.ItemDescription
			, 1 AS Iteration
	FROM	dbo.Item i1
	INNER JOIN (
				SELECT    TransactionID
						, MAX(RecordSeqNo) AS MaxRecordSeqNo
				FROM dbo.Item
				GROUP BY TransactionID
				) AS i2
	ON i2.TransactionID = i1.TransactionID
UNION ALL
SELECT		  i.TransactionID
			, i.RecordSeqNo AS RecordSeqNo
			, c.MaxRecordSeqNo
			, i.RecordSeqNo+(c.MaxRecordSeqNo*c.Iteration)
			, i.ItemDescription
			, Iteration+1
FROM		dbo.Item i 
INNER JOIN	CTE_Item c 
		ON	c.TransactionID	 = i.TransactionID
		AND c.RecordSeqNo	 = i.RecordSeqNo
		AND c.Iteration		<= @NumberOfIterations

)
SELECT * FROM CTE_Item
WHERE NextRecordSeqNo <> 0
ORDER BY TransactionID, NextRecordSeqNo

ROLLBACK TRAN