May 26, 2005 at 1:02 pm
I Need To Create a query to expand out a number of rows based on a value in one of the columns
This data:
CompanyIDItemDescriptionItemReferenceNumberItemQty
1$25 Dollars251
2$25 Dollars252
3$25 Dollars253
Should Be Expanded To One Row for each of the Values In The ItemQty Columb:
CompanyIDItemDescriptionItemReferenceNumberItemQty
1$25 Dollars251
2$25 Dollars252
2$25 Dollars252
3$25 Dollars253
3$25 Dollars253
3$25 Dollars253
Outside of cursoring through this in a stored procedure, has anyone done any “Cross Joins” or subqueries that does this?
Thanks,
Anton
May 26, 2005 at 1:06 pm
Select A.CompanyID, A.ItemDescription, A.ItemReferenceNumber, A.ItemQty from dbo.YourTable inner join dbo.Numbers N on A.ItemQty <= N.PkNumber
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
May 26, 2005 at 1:24 pm
Thanks For The Quick Reply,
I finally go it working by reversing changing the = otherwise it was bringing 8000-ItemQty records back for each record in the source table...
Cool...Thanks Again....
Select A.CompanyID, A.ItemDescription, A.ItemReferenceNumber, A.ItemQty
from MyTable A
inner join dbo.Numbers N on A.ItemQty >= N.PkNumber
May 26, 2005 at 1:33 pm
Sorry about that... forgot to test that response.
Glad you got it to work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply