Selecting records x number of times (depending on column value)

1

Hope someone can help.

DECLARE @test AS TABLE ( Name VARCHAR (30), Code VARCHAR (7), Value DECIMAL (15, 0) ) INSERT INTO @test VALUES ( 'Vladimir Putin', 'IN10025', '7' ) INSERT INTO @test VALUES ( 'Donald Trump', 'IN10045', '5' ) INSERT INTO @test VALUES ( 'Teresa May', 'IN10065', '3' ) SELECT * FROM @test

So in the above example, the output would be

Vladimir Putin IN10025 Vladimir Putin IN10025 Vladimir Putin IN10025 Vladimir Putin IN10025 Vladimir Putin IN10025 Vladimir Putin IN10025 Vladimir Putin IN10025 Donald Trump IN10045 Donald Trump IN10045 Donald Trump IN10045 Donald Trump IN10045 Donald Trump IN10045 Teresa May IN10065 Teresa May IN10065 Teresa May IN10065

The number of records is based on the value in column 3.

Any pointers appreciated and thanks in advance.

2

try this

;WITH CTE(NAME,CODE,VALUE,NO)
AS
(
SELECT *,1 FROM @test
UNION ALL
SELECT c.NAME,c.CODE,C.VALUE,c.NO+1 FROM CTE C WHERE C.nO< VALUE
)

SELECT * FROM CTE ORDER BY 1 option (maxrecursion 0)

1 Like
3

Excellent, many thanks.

And not a cursor in sight :grinning:

4

Probably answering the wrong question, but IF the aim is JUST to insert a row, multiple times, into a table - e.g. to make test data - then you can do

INSERT INTO @testTargetTable
VALUES 
(
'Vladimir Putin', 'IN10025'
)
GO 7
INSERT INTO @testTargetTable
VALUES 
(
'Donald Trump', 'IN10045'
)
GO 5
INSERT INTO @testTargetTable
VALUES 
(
'Teresa May', 'IN10065'
)
GO 3
1 Like
5

Not quite where I was going with it but it's still a useful trick, never knew you could do that.

It'll come in useful, I'm always needing to create test data so many thanks.

6

Its probably only something, and an available side-effect, in SSMS ...