Help with Sql Select query

select [lineno], part, qty  from orderslines where rec='S000001' order by [lineno]
produces
lineno      part      qty
1      10021      5.00
2      10370      2.00
3      10405      1.00

What I really want is multiple rows based upon the value in the qty column so the above would look like
1      10021      1.00    --- (5)
1      10021      1.00
1      10021      1.00
1      10021      1.00
1      10021      1.00

2      10370      1.00    --(2)
2      10370      1.00

3      10405      1.00  --(1)
LVL 1
bensonwalkerAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Oops. Try this:

with mynumbers as
(select row_Number() over(order by (select null)) as mynumber from sys.objects),
mytable as
(Select [lineno], part, Qty, row_number() over(partition by [lineno] order by (select null)) as myline
from orderslines 
cross join mynumbers
where rec='S000001' )
Select [lineno], part, 1 as myQty
from mytable
where myline <= Qty

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Which version of SQL Server?
0
 
bensonwalkerAuthor Commented:
2008 r2
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
UnifiedISCommented:
You can do it this way with a couple loops and  temp tables
DECLARE @orderslines Table (
	ID int identity,
	[lineno] int,
	part varchar(50),
	qty int
	)
INSERT INTO @orderslines ([lineno], part, qty)
SELECT 1, '10021', 5

INSERT INTO @orderslines ([lineno], part, qty)
SELECT 2, '10370', 2

INSERT INTO @orderslines ([lineno], part, qty)
SELECT 1, '10405', 1

SELECT *
FROM @orderslines

DECLARE @LoopCounter int
SELECT @LoopCounter = MIN(ID) FROM @orderslines
DECLARE @LineCount int
DECLARE @LoopCounter2 int

DECLARE @Lines Table (
	[lineno] int,
	part varchar(50)
	)

WHILE @LoopCounter <= (SELECT MAX(ID) FROM @orderslines)
	BEGIN
			SELECT @LineCount = qty
			FROM @orderslines
			WHERE ID = @LoopCounter
	
			SET @LoopCounter2 = 1			
			WHILE @LoopCounter2 <= @LineCount
				BEGIN
					INSERT INTO @Lines ([lineno], part)
					SELECT [lineno], part
					FROM @orderslines
					WHERE ID = @LoopCounter

					SET @LoopCounter2 = @LoopCounter2 + 1

					IF @LoopCounter2 <= @LineCount	
						CONTINUE
					ELSE
						BREAK
				END
		SET @LoopCounter = @LoopCounter + 1
		
		IF @LoopCounter <= (SELECT MAX(ID) FROM @orderslines)
			Continue
		ELSE
			BREAK
	END
SELECT L.[lineno], L.part, 1
FROM @Lines L

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
with mynumbers as
(select row_Number() over(order by (select null)) as mynumber from sys.objects),
mytable as
(Select [lineno], part, 1 as myQty, row_number() over(partition by [lineno] order by (select null)) as myline
from orderslines 
cross join mynumbers
where rec='S000001' )
Select [lineno], part, myQty
from mytable
where myline <= myQty

Open in new window

0
 
bensonwalkerAuthor Commented:
the above only returns one line for each part. If the part has a qty of 5 I need 5 lines not one.
0
 
bensonwalkerAuthor Commented:
Perfect just what I was looking for
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.