Suppose I have a set in SQL like this:
Product | Quantity
A 1
B 2
I want (in a single SELECT statement) to transform that to:
Product
A
开发者_StackOverflow社区 B
B
Can anyone point me towards (T-SQL preferably), a trick on how to do this?
You need an intermediary numbers table, or a table-valued function (if that option is available to you) which will produce numbers.
Assuming you had a Numbers table, which is populated like so:
Number
------
1
2
...
100000
(or as large as you need it to be, and there are efficient mechanisms for generating a numbers table of large size)
You would then issue the following query:
select
p.Product
from
Products as p
inner join Numbers as n on n.Number <= p.Quantity
This would produce the result you want.
A numbers table is incredibly helpful in SQL, and Itzik Ben-Gan goes into it and other great querying techniques in his books (listed on his website). I highly recommend them.
select ProductName, Quantity from Product
ProductName Quantity
A 1
B 2
C 4
Here is one select, as you wish (sql server 2005):
with result (ProductName, Q)
as
(
select ProductName, 1 from Product where Quantity > 0
union all
select p1.ProductName, result.q + 1 from Product p1
inner join result on result.ProductName = p1.ProductName
where result.q < p1.Quantity
)
select p2.ProductName from Product p2
inner join result on result.ProductName = p2.ProductName
order by 1
OPTION (MAXRECURSION 0);
ProductName
A
B
B
C
C
C
C
Try CURSOR??
DECLARE @Cursor CURSOR
DECLARE @Product varchar(50)
SET @Cursor = CURSOR FOR
SELECT Product FROM dbo._YourTable
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @Product
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count int
SET @Count = 0
DECLARE @Quantity int
SET @Quantity = SELECT Quantity FROM dbo._YourTable WHERE Product = @Product
WHILE @Count < @Quantity
BEGIN
SELECT @Product as Product
@Count = @Count + 1
END
FETCH NEXT FROM @Cursor INTO @Product
END
DEALLOCATE @Cursor
精彩评论