Answered by:
How to repeat rows based on column value

Question
-
Hi all,
I have a query that gives me a result with a column value for example 4.
I now want to repeat this row 4 times with a new column that calculated from 1 - 4.
Or when column value is 3 I want to repeat row 3 times with new column name 1-3
Can anyone help me with this please? :)
Thursday, April 30, 2015 12:38 PM
Answers
-
Hi Yvanlathem,
To achieve your requirement, you can reference the recursive approach as below.
DECLARE @T TABLE(Col1 CHAR(1),Col2 INT) INSERT INTO @T VALUES('A',1),('B',2),('C',3)
;WITH Cte([Char],[Repeat]) AS ( SELECT Col1,Col2 FROM @T UNION ALL SELECT [Char],[Repeat]-1 FROM Cte WHERE [Repeat]>1 ) SELECT * FROM Cte ORDER BY [Char],[Repeat] OPTION(MAXRECURSION 0) /* Output Char Repeat A 1 B 1 B 2 C 1 C 2 C 3 */
If you have any question, feel free to let me know.
Eric Zhang
TechNet Community Support- Edited by Eric__Zhang Friday, May 1, 2015 6:56 AM
- Proposed as answer by Eric__Zhang Thursday, May 7, 2015 8:47 AM
- Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
Friday, May 1, 2015 6:55 AM -
Here is how you can do it
CREATE TABLE TestTable ( ID INT IDENTITY(1,1), Col1 varchar(10), Repeats INT ) INSERT INTO TESTTABLE VALUES ('A',2), ('B',4),('C',1),('D',0) WITH x AS ( SELECT TOP (SELECT MAX(Repeats)+1 FROM TestTable) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_columns ORDER BY [object_id] ) SELECT * FROM x CROSS JOIN TestTable AS d WHERE x.rn <= d.Repeats ORDER BY Col1;
SQL Fiddle Example
Output:
Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.
My Profile on Microsoft ASP.NET forum- Edited by Anuj Tripathi Thursday, April 30, 2015 1:06 PM output added
- Proposed as answer by Reshma Mahabir-Poeran Thursday, April 30, 2015 5:50 PM
- Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
Thursday, April 30, 2015 1:02 PM -
CREATE TABLE test(Col varchar(20), val int) INSERT INTO test VALUES ('aaa',4), ('bbb',3),('ccc',1) select Col,num from test cross apply(values (1),(2),(3),(4),(5),(6),(7),(8),(9)) d(num) where num<=val drop TABLE test
- Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
Friday, May 1, 2015 1:19 PM
All replies
-
You can use a Tall table for this.
SELECT mt.*, ROW_NUMBER() OVER (PARTITION BY mt.ID ORDER BY t.n) AS GroupRow FROM dbo.MyTable mt JOIN Tally t ON t.n <= mt.NumColumn
Jason Long
Thursday, April 30, 2015 12:59 PM -
Here is how you can do it
CREATE TABLE TestTable ( ID INT IDENTITY(1,1), Col1 varchar(10), Repeats INT ) INSERT INTO TESTTABLE VALUES ('A',2), ('B',4),('C',1),('D',0) WITH x AS ( SELECT TOP (SELECT MAX(Repeats)+1 FROM TestTable) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_columns ORDER BY [object_id] ) SELECT * FROM x CROSS JOIN TestTable AS d WHERE x.rn <= d.Repeats ORDER BY Col1;
SQL Fiddle Example
Output:
Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.
My Profile on Microsoft ASP.NET forum- Edited by Anuj Tripathi Thursday, April 30, 2015 1:06 PM output added
- Proposed as answer by Reshma Mahabir-Poeran Thursday, April 30, 2015 5:50 PM
- Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
Thursday, April 30, 2015 1:02 PM -
Hi Yvanlathem,
To achieve your requirement, you can reference the recursive approach as below.
DECLARE @T TABLE(Col1 CHAR(1),Col2 INT) INSERT INTO @T VALUES('A',1),('B',2),('C',3)
;WITH Cte([Char],[Repeat]) AS ( SELECT Col1,Col2 FROM @T UNION ALL SELECT [Char],[Repeat]-1 FROM Cte WHERE [Repeat]>1 ) SELECT * FROM Cte ORDER BY [Char],[Repeat] OPTION(MAXRECURSION 0) /* Output Char Repeat A 1 B 1 B 2 C 1 C 2 C 3 */
If you have any question, feel free to let me know.
Eric Zhang
TechNet Community Support- Edited by Eric__Zhang Friday, May 1, 2015 6:56 AM
- Proposed as answer by Eric__Zhang Thursday, May 7, 2015 8:47 AM
- Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
Friday, May 1, 2015 6:55 AM -
CREATE TABLE test(Col varchar(20), val int) INSERT INTO test VALUES ('aaa',4), ('bbb',3),('ccc',1) select Col,num from test cross apply(values (1),(2),(3),(4),(5),(6),(7),(8),(9)) d(num) where num<=val drop TABLE test
- Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
Friday, May 1, 2015 1:19 PM -
why would anyone want to destroy 1NF like this?
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Friday, May 1, 2015 8:35 PM -
You can try this solution also using the
master.dbo.spt_values
create table MyTable (QTY int, ITEM varchar(10)) insert into MyTable Values (1, 'aaa'), (2, 'bbb'), (4, 'ccc') SELECT t1.*, t2.number + 1 RepeatNumber FROM MyTable t1 JOIN master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.QTY
Live Demo- Proposed as answer by Suraj Kumar Programmer Friday, February 21, 2020 5:41 PM
Friday, February 21, 2020 5:39 PM