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
Asked
Active
Viewed 276 times
2
-
2Does Dave have multiple rows in the table? And you want to sum up the various Qty's? It's hard to tell from your question. – Steve K Apr 27 '11 at 17:28
-
@Steve "dave" is 1 record in the qty colum the value is 25 – Nathan Apr 27 '11 at 17:32
1 Answers
2
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
Community
- 1
- 1
Martin Smith
- 438,706
- 87
- 741
- 845
-
-
@Nathan - You don't need to know. As long as you make sure that the Numbers table is at least as big as any conceivable value it might have (and you can enforce this with check constraints on `Qty`). You just join on `number <= Qty` – Martin Smith Apr 27 '11 at 17:36