# 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

• 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 Friday, May 1, 2015 6:56 AM
• Proposed as answer by Thursday, May 7, 2015 8:47 AM
• Marked as answer by 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:

My Profile on Microsoft ASP.NET forum

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 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:

My Profile on Microsoft ASP.NET forum

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 Friday, May 1, 2015 6:56 AM
• Proposed as answer by Thursday, May 7, 2015 8:47 AM
• Marked as answer by 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 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.QTYLive Demo```
Friday, February 21, 2020 5:39 PM