CODEWITHSUNDEEP
sql-server
Nathan
Nathan

Reputation: 35

get multiple records from one record

i have a table with 2 columns Name as string ,Qty as integer i need amount of each record X the Qty Field for example 

Name   | Qty
-----------
Dave   | 25
Nathan | 10
Chaim  | 8

i need from Dave 25 records from nathan 10 and chaim 8

Upvotes: 2

Views: 289

Answers (1)

Martin Smith
Martin Smith

Reputation: 453037

Not sure of the use case for this requirement but you can do this with the aid of a numbers table. I use master..spt_values below for demo purposes but you should create a permanent one using one of the techniques in this answer.

;WITH T(Name,Qty) AS
(
SELECT 'Dave',25 union all
SELECT 'Nathan',10 union all
SELECT 'Chaim',8
), Numbers AS
(
SELECT number
FROM master..spt_values 
WHERE   type='P' AND number > 0
)
SELECT Name
FROM T 
JOIN Numbers ON  number  <= Qty

Upvotes: 2

Related Questions