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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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
 

Answer 5

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

Answer 6

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