Quantity to Row

let say i have this Records

Table1 = ItemCode + Quantity

Table1
ItemCode1, 2
ItemCode2, 3

when i select it, it make Quantity to Row value in the Result

ItemCode1
ItemCode1
ItemCode2
ItemCode2
ItemCode2


this is for Barcode program because we need to use it as a row not using Quantity

thanks,
erick

What database application are you using? Access, SQL Server, Oracle?

Sqlsever 2005

Have you considered that logic might belong in the UI?

The DB says Item1 has a value of 2 - and you want the UI to show 2. Why not read the ITEM1 and 2 into variables in your logic building this display and simply put the row twice?

I can't see a simply SQL join that would see the "2" and know to duplicate that 2 times. Oh...maybe I do...

Try this - I don't have time to test it...

Code:

---

Declare @Copy Table (CP int)
Insert into @Copy values (1)
Insert into @Copy values (2)
Insert into @Copy values (3)
Insert into @Copy values (4)
-- you need to do this up to the max value in your table
-- or make it a loop in the final SPROC

Select ItemCode From Table1 T1
Left Join @Copy CO on CO.CP<=T1.Quantity

---

thx sz

Have you considered that logic might belong in the UI? -> twice, maybe more now :D

i try using CURSOR and confuse :D

-- or make it a loop in the final SPROC -> sz, i'm confuse about this part, can u elaborate more

have you tried my @Copy query?

yes i have

i got 7 rows in Table1, the query give me 17 results..i'm not sure why

Works fine in this example - first I create some test data and then SELECT from that.

Code:

---

Declare @TestData Table (ItemNum varchar(10), Quantity int)

Insert into @TestData values ('AAA',2)
Insert into @TestData values ('BBB',1)
Insert into @TestData values ('CCC',3)

select * from @TestData

Declare @Copy Table (CP int)
Insert into @Copy values (1)
Insert into @Copy values (2)
Insert into @Copy values (3)
Insert into @Copy values (4)
-- you need to do this up to the max value in your table
-- or make it a loop in the final SPROC

Select * from @TestData
Left Join @Copy CO on CP<=Quantity

---

and that returns

Code:

---

ItemNum    Quantity
---------- -----------
AAA        2
BBB        1
CCC        3

(3 row(s) affected)

ItemNum    Quantity    CP
---------- ----------- -----------
AAA        2          1
AAA        2          2
BBB        1          1
CCC        3          1
CCC        3          2
CCC        3          3

(6 row(s) affected)

---

thanks sz


sorry for late reply,

yes it works..but i just realized about it that the ItemNum got child records for the Barcode things

so it required new table

Code:

---

Declare @Details Table (ItemNum varchar(10), Barcode varchar(10), Quantity int)
Insert into @Details values ('AAA', 'A1', 1)
Insert into @Details values ('AAA', 'A2', 1)
Insert into @Details values ('AAA', 'A3', 1)
Insert into @Details values ('BBB', 'B1', 1)
Insert into @Details values ('CCC', 'C1', 1)

---

it need to select the Barcode in ASC Order, the quantity for the barcode is always 1

thanks

i'm thinking about this, use new column like identity but it started from 1 whenever itemcode is changing

Code:

---

Declare @Details Table (Num int, ItemNum varchar(10), Barcode varchar(10), Quantity int)
Insert into @Details values (1, 'AAA', 'A1', 1)
Insert into @Details values (2, 'AAA', 'A2', 1)
Insert into @Details values (3, 'AAA', 'A3', 1)
Insert into @Details values (1, 'BBB', 'B1', 1)
Insert into @Details values (1, 'CCC', 'C1', 1)

---

and using the query and get the result

Code:

---


Select * from @TestData t1
Left Join @Copy CO on CP<=t1.Quantity
left join @details t2 on t1.ItemNum = t2.itemnum and co.cp = t2.num


ItemNum    Quantity    CP          Num        ItemNum    Barcode    Quantity
---------- ----------- ----------- ----------- ---------- ---------- -----------
AAA        2          1          1          AAA        A1        1
AAA        2          2          2          AAA        A2        1
BBB        1          1          1          BBB        B1        1
CCC        3          1          1          CCC        C1        1
CCC        3          2          NULL        NULL      NULL      NULL
CCC        3          3          NULL        NULL      NULL      NULL

(6 row(s) affected)

---

but if anyone any other method, plz let me know

thanks