Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 925
  • Last Modified:

Multiply a record based on a field?

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
sopheak
Asked:
sopheak
1 Solution
 
chiragkhabariaCommented:
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
 
bamboo7431Commented:
--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
 
sopheakAuthor Commented:
thanks,
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now