Dears,
I want to loop thru sql rows and repeat them depending on column value as shown below:
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.
i don't understand what do you mean, could you help with code please.
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==================================
thanks alot, but the result is not the same as i need, if you see the order should be as following:
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...
1 Like
thats it, thank you very much.
Glad to help. 
1 Like