SQLTeam.com | Weblogs | Forums

* Loop thru sql rows depending on column value


#1

Dears,

I want to loop thru sql rows and repeat them depending on column value as shown below:

sql-repeat


#2

You'd never want to use a loop or cursor for something like this. A tally table or function would be orders of magnitude more efficient.


#3

i don't understand what do you mean, could you help with code please.


#4

Sure... First code block is a Tally function... Second code block is referencing it in a query...

CREATE FUNCTION dbo.tfn_Tally
/* ============================================================================
07/20/2017 JL, Created. Capable of creating a sequense of rows 
				ranging from -10,000,000,000,000,000 to 10,000,000,000,000,000
============================================================================ */
(
	@NumOfRows BIGINT,
	@StartWith BIGINT 
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
	WITH 
		cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),	-- 10 rows
		cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),								-- 100 rows
		cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),								-- 10,000 rows
		cte_n4 (n) AS (SELECT 1 FROM cte_n3 a CROSS JOIN cte_n3 b),								-- 100,000,000 rows
		cte_Tally (n) AS (
			SELECT TOP (@NumOfRows)
				(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) + @StartWith
			FROM 
				cte_n4 a CROSS JOIN cte_n4 b													-- 10,000,000,000,000,000 rows
			)
	SELECT 
		t.n
	FROM 
		cte_Tally t;
GO

...

IF OBJECT_ID('tempdb..#OrderData', 'U') IS NOT NULL 
DROP TABLE #OrderData;

-- populate some test data...
CREATE TABLE #OrderData (
	[Order] int NOT NULL,
	Item VARCHAR(20) NOT NULL,
	Number_Of_Repeat INT NOT NULL 
	);
INSERT #OrderData ([Order], Item, Number_Of_Repeat) VALUES 
	(1, 'Fish', 2),
	(2, 'Bread', 1),
	(3, 'Cheese', 3),
	(4, 'Chips', 2),
	(5, 'Beef', 3);

--=========================================================

-- solution query...
SELECT
    ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
	od.[Order], 
	od.Item
FROM
    #OrderData od
	CROSS APPLY dbo.tfn_Tally(od.Number_Of_Repeat, 1) t;

results...

ID                   Order       Item
-------------------- ----------- --------------------
1                    1           Fish
2                    1           Fish
3                    2           Bread
4                    3           Cheese
5                    3           Cheese
6                    3           Cheese
7                    4           Chips
8                    4           Chips
9                    5           Beef
10                   5           Beef
11                   5           Beef

#5

==================================
thanks alot, but the result is not the same as i need, if you see the order should be as following:

sss


#6

Not sure why it would matter, but the following adjustment to the ROW_NUMBER() function will give you the desired sort...

-- solution query...
SELECT
	ID = ROW_NUMBER() OVER (ORDER BY t.n, od.[Order]),
	od.[Order], 
	od.Item
FROM
    #OrderData od
	CROSS APPLY dbo.tfn_Tally(od.Number_Of_Repeat, 1) t;

Edit: Just be aware that adding the sort operation makes the query 3X more expensive than it would be without it...


#7

thats it, thank you very much.


#8

Glad to help. :slight_smile: