How to repeat rows based on column value

_{Thursday, April 30, 2015 12:38 PM}

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 1:02 PM ✅Answered}

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

_{Friday, May 1, 2015 6:55 AM ✅Answered | 1 vote}

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

_{Friday, May 1, 2015 1:19 PM ✅Answered}

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
 

_{Thursday, April 30, 2015 12:59 PM}

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

_{Friday, May 1, 2015 8:35 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, February 21, 2020 5:39 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