15,901,426 members
See more:
Table1

id player repeat
1 Sachin 3
2 Ganguly 2
3 Dravid 4

Consider the above table.Now I need to create a new table with the following output:

id player repeat
1 Sachin 1
1 Sachin 1
1 Sachin 1
2 Ganguly 1
2 Ganguly 1
3 Dravid 1
3 Dravid 1
3 Dravid 1
3 Dravid 1

Here in Table1, if repeat is 3 ,then in second table repeat will be 3 times with value 1.

Thanks

What I have tried:

I have tried rank ,dense rank cube etc but no reslult
Posted
Updated 19-Feb-18 9:15am
Naga Sindhura 21-Feb-18 5:24am
try to use auxillary table table mentioned in the following link.
https://stackoverflow.com/questions/33327837/repeat-rows-n-times-according-to-column-value
Naga Sindhura 21-Feb-18 5:32am
one more thing master.dbo.spt_values table to repeat the columns for n times

CREATE TABLE RepeatRows (
Id int identity(1,1),
RepeatText varchar(100),
RepeatCount int
)
INSERT INTO RepeatRows SELECT 'Repeat 3 Times', 3
INSERT INTO RepeatRows SELECT 'Repeat 2 Times', 2
INSERT INTO RepeatRows SELECT 'Repeat 4 Times', 4
INSERT INTO RepeatRows SELECT 'Only once', 1

SELECT t1.*, t2.number + 1 RepeatNumber
FROM RepeatRows t1
JOIN master.dbo.spt_values t2 on t2.type = 'P' and t2.number < t1.RepeatCount

## Solution 1

You can use CROSS APPLY with a numbers table. This article shows you one way to create a numbers table Generating a Sequence in SQL[^] or more in depth here The SQL Server Numbers Table, Explained - Part 1[^]
Then you can do something like this
SQL
```create table #src (id int identity(1,1), player varchar(20), [repeat] int)
insert into #src (player, [repeat]) values
('Sachin', 3),
('Ganguly', 2),
('Dravid', 4)

declare @m int = (SELECT Max([repeat]) from #src)

;WITH q AS
(
SELECT  1 AS num
UNION ALL
SELECT  num + 1
FROM    q
WHERE num < @m
)
SELECT num INTO #nos FROM q

SELECT A.*
FROM #src A
CROSS APPLY #nos B
WHERE B.num <= A.[repeat]
ORDER BY id```
Giving the following results:
```1	Sachin	3
1	Sachin	3
1	Sachin	3
2	Ganguly	2
2	Ganguly	2
3	Dravid	4
3	Dravid	4
3	Dravid	4
3	Dravid	4```
[EDIT] just re-read my solution against your post - you probably want that last select to be
SQL
`SELECT A.id, A.player, 1 as [repeat]`
to get the exact results you mentioned

v2
Maciej Los 19-Feb-18 10:06am
Good one! I think it's possible to achieve that in simpler manner ;) See my solution.

## Solution 2

In addition to solution 1 by CHill60[^], i'd prefer to use Common Table Expressions only (without using CROSS APPLY). For example:

SQL
```<pre>DECLARE @tmp TABLE(id INT IDENTITY(1,1), player VARCHAR(30), [repeat] INT)

INSERT INTO @tmp(player, [repeat])
VALUES('Sachin', 3),
('Ganguly', 2),
('Dravid', 4)

;WITH CTE AS
(
--initial part
SELECT id, player, 1 As CurrVal, [repeat], 1 AS myCounter
FROM @tmp
WHERE [repeat]>0
--recursive part
UNION ALL
--myCounter is used to define the condition to exit from loop
SELECT id, player, 1 As CurrVal, [repeat], myCounter + 1 AS myCounter
FROM CTE
WHERE myCounter<[repeat]
)
SELECT id, player, CurrVal AS [repeat]
FROM CTE
ORDER BY id, myCounter   ```

Result:
```id	player	repeat
1	Sachin	1
1	Sachin	1
1	Sachin	1
2	Ganguly	1
2	Ganguly	1
3	Dravid	1
3	Dravid	1
3	Dravid	1
3	Dravid	1```

Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL) | Microsoft Docs[^]
Recursive Queries Using Common Table Expressions[^]