Click here to Skip to main content
15,901,426 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

Please help with the solution.

Thanks

What I have tried:

I have tried rank ,dense rank cube etc but no reslult
Posted
Updated 19-Feb-18 9:15am
Comments
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

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
 
Share this answer
 
v2
Comments
Maciej Los 19-Feb-18 10:06am    
Good one! I think it's possible to achieve that in simpler manner ;) See my solution.
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


For further details, please see:
Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL) | Microsoft Docs[^]
Recursive Queries Using Common Table Expressions[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900