How to select as much records as indicated by the value from database

Question

I have one table in relational database Sybase ASE, with few columns. Three of them looks like this example:

 _____________
|   Product   |
---------------
| ProductId   |

| Name        |

| Quantity    |
 _____________

So we have some records:

__________________________________
| ProductId | Name    | Quantity |
----------------------------------
|    1      | pants   |     2    |
|    2      | shirt   |     1    |
|    3      | sweater |     3    |
----------------------------------

I need to get every name as many times as 'Quantity' of this product.

So the result should looks like:

pants
pants
shirt
sweater
sweater
sweater

If somebody have any idea how can I do this, please help me.

EDIT 2014-01-24 14:17 UTC+1

I'd like to thanks everybody. Gordon's solution is realy nice, but for my situation (bigger Quantity) I can't use that sql. I try do somethnig like 333kenshin's and simon's solutions but without cursor. I do somthnig like this:

IF OBJECT_ID('#TEMP') is not null
DROP TABLE #TEMP

create  TABLE #TEMP (Name varchar(255)) 

DECLARE @Name varchar(255)
DECLARE @Quant INT
DECLARE @prodId INT
SET @prodId = 1

WHILE (EXISTS(SELECT 1 FROM product WHERE productID = @prodId))
BEGIN
    SELECT
        @Name = Name
        @Quant = Quantity
    FROM Product

    DECLARE @i INT
    SET @i = 1
    WHILE @i <= @Quant
        BEGIN
            insert into #TEMP
            values(@Name)
            SELECT @i=@i+1
        END
    SELECT @prodId = @prodId + 1
END

select * from #TEMP
drop table #TEMP

For me, and my DB it was fastest solution. So thanks a lot for every answers.

score 1 · Answer 1 · answered Jan 23 '14 at 17:10

To do this, you need a series of integers. You can generate one manually:

select p.name
from product p join
     (select 1 as n union all select 2 union all select 3 union all select 4
     ) n
     on n.n <= p.quantity;

This will work if quantity is not too big and you can put in the values in n.

score 0 · Answer 2 · answered Jan 23 '14 at 17:11

You could use a temporary table. Select all your product rows, then loop through each row returned. For each product, loop Quantity times and in the loop insert the product into the temp table. I'm not a Sybase user, so I can't give you the syntax but you would have a stored procedure which would do something like:

select all rows from product into a cursor
for each row
    for i = 1 to row.Quantity
        insert into temp (Name) values (row.Name)  
    next
end loop

select * from temp and return it

It's a pretty eccentric requirement, though.

EDIT: Gordon's solution is neat though! If n never gets too big I'd go with that!

score 0 · Accepted Answer · answered Jan 24 '14 at 06:16

The correct way to do this is temp table + cursors:

create a temp table
create cursor to iterate through Product table
within the cursor, create an inner WHILE loop
exit the loop and finally select the temp table

The following isn't 100% correct Sybase syntax, but it's pretty close.

-- 1: temp table
select productName into #TEMP

-- 2: cursor
declare
    @productName char(10),
    @quantity int
declare ProductRead CURSOR for
select
    productName,
    quantity
from
    Product

OPEN ProductRead
FETCH ProductRead
INTO
    @productName,
    @quantity

WHILE (@@sqlstatus=0)
BEGIN

-- 3: inner for loop
DECLARE @i INT
SET @i = 1
WHILE @i <= @quantity
BEGIN
insert @productName into #TEMP
END

END

-- 4: final result set 
select productName from #TEMP

How to select as much records as indicated by the value from database

3 Answers3