Quantity to Row

[erickwidya]

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

[alex_read]

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

[erickwidya]

Sqlsever 2005

[szlamany]

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...

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

[erickwidya]

thx sz

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

i try using CURSOR and confuse

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

[szlamany]

have you tried my @Copy query?

[erickwidya]

yes i have

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

[szlamany]

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

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

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)

[erickwidya]

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

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

[erickwidya]

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

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

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