Duplicating Records

[chamilz]

I have a table which has records like this,

order_no line_no color size qty
888 1 202 900 20
888 2 202 910 10
888 3 202 930 6
888 4 405 900 8
888 5 405 910 21
888 6 405 930 40


Now i need to create a report using the above table and need the result set like below,
order_no line_no color size qty


888 1 202 900 20
888 1 202 910 10
888 1 202 930 6

888 2 202 900 20
888 2 202 910 10
888 2 202 930 6

888 3 202 900 20
888 3 202 910 10
888 3 202 930 6

888 4 405 900 8
888 4 405 910 21
888 4 405 930 40

888 5 405 900 8
888 5 405 910 21
888 5 405 930 40

888 6 405 900 8
888 6 405 910 21
888 6 405 930 40


I tried using self-merge to get this result, but i couldnt.
Any advice would greatly appreciate.

Thanks.

 

[gmmastros]

Does this work for you?

-- Setting up temp data.
Declare @Table Table(order_no Integer, line_no Integer, color Integer, size Integer, qty Integer)

Insert Into @Table Values(888, 1, 202, 900, 20)
Insert Into @Table Values(888, 2, 202, 910, 10)
Insert Into @Table Values(888, 3, 202, 930, 6)
Insert Into @Table Values(888, 4, 405, 900, 8)
Insert Into @Table Values(888, 5, 405, 910, 21)
Insert Into @Table Values(888, 6, 405, 930, 40)

-- Returning records you want.
Select 	A.order_no, A.line_no, A.Color, B.Size, B.qty
from 	@Table A Inner Join @Table B On A.order_no = B.Order_no
			And A.Color = B.Color
Order By A.order_no, A.Line_No

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AtomicWedgie]

Hello,

I don't know if this is the easiest way but it does do the trick.

Sorry if I have not written it very clear:


select tabletest.order_no
, tabletest.line_no
, tabletest.color
, [size]
, qty
from testtable
, (select order_no
, line_no
, a.color
from (select *
from (select order_no
, line_no
from testtable) as x
, (select color
from testtable) as y) as a
, (select min(line_no) as minline
, max(line_no) as maxline
, color
from testtable
group by color) as b
where a.color = b.color
and (line_no between minline and maxline)) as tabletest
where testtable.line_no = tabletest.line_no
and testtable.color = tabletest.color
and testtable.line_no = tabletest.line_no


Regards,

Atomic Wedgie

 

[AtomicWedgie]

haha,

the one from gmmastros is cleary more easy

atomic wegdie

 

[gmmastros]

Let's just hope that chamilz replies back. It works on the test data, but you never know...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chamilz]

Thanks George and Atomic for your help...

George it was a simple and short answer to my question....thats what i wanted.Works like charm!!

Thasnk again both of you for providing quick sulutions.