How about a temporary table:
create table #t2
(
order_no varchar(255),
order_line_no int,
long_description varchar(8000),
done bit
)
Insert Into #t2 (order_no, order_line_no, long_description, done)
Select order_no,
order_line_no,
long_description,
0
From #t1
Where line_type = 'p'
Declare @i int
Set @i = 1
While (Select count(*) From #t2 where Done = 0) > 1
Begin
Update t2
Set t2.long\_description = t2.long\_description + ' ' + t1.long\_description
From #t1 t1
inner join
#t2 t2
On t1.order\_no = t2.order\_no
And t1.order\_line\_no = t2.order\_line\_no + @i
And t1.line\_type <> 'P'
And t2.done = 0
Update t2
Set t2.done = 1
From #t1 t1
inner join
#t2 t2
On t1.order\_no = t2.order\_no
And t1.order\_line\_no = t2.order\_line\_no + @i
And t1.line\_type = 'P'
Set @i = @i + 1
End
You could also use a cursor. Mike
