How do I find the below column ?

Question

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 18-Feb-18 20:54pm

Member 13684763

Updated 19-Feb-18 9:15am

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2018-02-19T00:55:00

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

Maciej Los · Answer 2 · 2018-02-19T04:03:00

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[^]

How do I find the below column ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0