Breakdown Quantity Field - 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

Breakdown Quantity Field

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-09-20 : 12:41:54

I'll give a basic example of my table.
OrderID, ProductID, Quantity
1,1,4

I would like to display the above row 4x (hence qty of 4)
1,1,"item 1"
1,1,"item 2"
1,1,"item 3"
1,1,"item 4"

Any suggestions? Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-20 : 13:13:05

this would be best done in the front end because it's essentially a formatting issue.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 13:23:29

This is a basic solution to your basic example. When you want to scroll thru
and entire table, it'll get much harder.

Jim

SET NOCOUNT ON

DECLARE @tbl TABLE (OrderID int,ProductID int, Quantity int)
DECLARE @newTbl TABLE (OrderID int,ProductID int,Quantity varchar(10))
DECLARE @qty int
SET @qty = 1

INSERT INTO @tbl
select 1,1,4

WHILE @qty <= 4
BEGIN
INSERT INTO @newTbl

select orderid,productid,'item ' + CONVERT(varchar(2),@qty)
from @tbl

set @qty = @qty + 1
END

SELECT * FROM @newTbl

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-09-20 : 13:30:07

Spirit 1, frontend is a good idea since I am using reporting services (granted im not the best at it, more of a crystal reports user)
JimF, I might go about doing it this way just because I have a field shipping# which has the value "123/124/125/126" attached to the qty of 4. I'll let you know how I make out.

Thanks Everyone!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 03:14:17

<<
I have a field shipping# which has the value "123/124/125/126"
>>

http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 03:16:06

quote:
Originally posted by jimf

This is a basic solution to your basic example. When you want to scroll thru
and entire table, it'll get much harder.

Jim

SET NOCOUNT ON

DECLARE @tbl TABLE (OrderID int,ProductID int, Quantity int)
DECLARE @newTbl TABLE (OrderID int,ProductID int,Quantity varchar(10))
DECLARE @qty int
SET @qty = 1

INSERT INTO @tbl
select 1,1,4

WHILE @qty <= 4
BEGIN
INSERT INTO @newTbl

select orderid,productid,'item ' + CONVERT(varchar(2),@qty)
from @tbl

set @qty = @qty + 1
END

SELECT * FROM @newTbl

Dont need WHILE. Use Cross Join



DECLARE @tbl TABLE (OrderID int,ProductID int, Quantity int)
DECLARE @newTbl TABLE (OrderID int,ProductID int,Quantity varchar(10))
DECLARE @qty int 
SET @qty = 1



INSERT INTO @tbl
select 1,1,4

select 
	t1.orderId,t1.productid, 'item '+cast(t2.n as varchar(10)) as items from @tbl t1 
cross join 
	(select 1 as n union all select 2 union all select 3 union all select 4) 
	t2
where t1.Quantity>=t2.n

Madhivanan

Failing to plan is Planning to fail

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-09-21 : 10:26:08

Cross join is awesmome. Thanks! Is there anyway I can add a row # to the cross join... so when I join on it i can see which line # it is. almost like a order_details id, "line number".

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-21 : 10:53:13

And this will handle a pretty big n without having to add more unions

DECLARE @tbl TABLE (OrderID int,ProductID int, Quantity int)
DECLARE @newTbl TABLE (OrderID int,ProductID int,Quantity varchar(10))
DECLARE @qty int
SET @qty = 1

INSERT INTO @tbl
select 1,1,4
union all
select 1,2,27

select
t1.orderId,t1.productid, 'item '+cast(t2.n as varchar(10)) as items from @tbl t1
cross join

(select number as n from master..spt_values where type = 'P' and number > 0)
t2
where t1.Quantity>=t2.n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources