Generate many rows from 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate many rows from 1

Author

Topic

pdawg555
Starting Member

4 Posts

Posted - 2012-02-16 : 23:51:37

I have a view that looks like this

PartNumber Qty PackageQty
abc 100 10

I am using it to print labels, so customer ordered 100 and we package in boxes of 10 so I need 10 labels. My label software needs to see each label as its own record

So I need to modify the sql in the view to make it show that record X times, where X is Qty/PackageQty

any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-16 : 23:54:28

CROSS JOIN your table with F_TABLE_NUMBER_RANGE

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 13:41:08

[code]
;With Part_Details
(
SELECT PartNumber,Qty, PackageQty,1 AS Ord
FROM table

UNION ALL

SELECT PartNumber,Qty, PackageQty,Ord + 1
FROM Part_Details
WHERE Ord + 1<=Qty/PackageQty

)

SELECT PartNumber
FROM Part_Details
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdawg555
Starting Member

4 Posts

Posted - 2012-02-18 : 18:32:46

Hello, Thank you so much! I am trying to cross join the f_table but haven't been able to figure out how to pass in a computation or variable...

SELECT     TOP (100) PERCENT dbo.RawReleases.RequiredShipDate, dbo.RawReleases.CustomerItemNo, dbo.RawReleases.DueDate, 
                      dbo.RawReleases.DueQty, dbo.RawReleases.SupplierNo, dbo.RawReleases.FinalDestPlantNo, dbo.RawReleases.ReleaseID, 
                      dbo.Part_lookup.PackageQty, dbo.RawReleases.PONo, F_TABLE_NUMBER_RANGE_1.NUMBER AS LabelNo                      
FROM         dbo.Part_lookup RIGHT OUTER JOIN
                      dbo.RawReleases ON dbo.Part_lookup.PartNo = dbo.RawReleases.CustomerItemNo CROSS JOIN
                      dbo.F_TABLE_NUMBER_RANGE(1, dbo.RawReleases.DueQty / dbo.Part_lookup.PackageQty) AS F_TABLE_NUMBER_RANGE_1

pdawg555
Starting Member

4 Posts

Posted - 2012-02-18 : 18:33:51

error says the multi-part identifier could not be bound

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-18 : 20:51:52

you tried mine?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdawg555
Starting Member

4 Posts

Posted - 2012-02-20 : 09:05:25

Yes it did work! thank you, I used visakh16's method. I just needed to add an "AS" after after "With Part_Details"

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 11:27:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources