Solved

# Multiply a record based on a field?

Posted on 2007-07-24
864 Views
Last Modified: 2008-01-09
I have table1 with 2 columns (FruitName, Qty) see the sample data.

FruitName      Qty
Apple             4
Orange          2

I need a way to insert the above data into table2 (FruitName, Qty) so that the quantity equates to a record.  So, in the case of Apple with the quantity of 4, I need table2 to have 4 records for apple, with the updated qty of 1.

FruitName      Qty
Apple             1
Apple             1
Apple             1
Apple             1
Orange          1
Orange          1

thanks,
sopheak

0
Question by:sopheak
3 Comments

LVL 6

Expert Comment

Insert Into Table2 (Fruitname,Qty)
Select FruitName, 1 From YourTable t
inner join
(
Select Distinct  Number From Master..spt_values
Where Number >0
) as  i
on t.qty >= i.number
0

LVL 4

Accepted Solution

--cursor-based solution
SET NOCOUNT ON
DECLARE input CURSOR FAST_FORWARD
FOR SELECT FruitName, Qty FROM table1
DECLARE @i int, @FruitName varchar(10), @Qty int
OPEN input
FETCH NEXT FROM input INTO @FruitName, @Qty
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @i=0
WHILE @i<@Qty BEGIN
INSERT INTO table2 VALUES(@FruitName,1)
SET @i=@i+1
END
FETCH NEXT FROM input INTO @FruitName, @Qty
END
SELECT * FROM table2
0

Author Comment

thanks,
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

#### 760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!