Query to concatenate multiple rows based on sequence in results set

Alex Lush

Morning All, I am trying to produce a query for a report that concatenates data from multiple rows. Easiest if I explain what I have, and what I need to end up with! This is Sage Line 500 data by the way. The table I am querying contains sale order lines as follows:

order_no order_line_no line_type product long_description
100001 1 P 47020 1st description for 47020
100001 2 C 2nd line description for 47020
100001 3 P 47030 1st description for 47030
100001 4 C 2nd line description for 47030
100001 5 P 47040 1st description for 47040
100001 6 C 2nd line description for 47040
100001 7 P 47050 1st description for 47050
100001 8 C 2nd line description for 47050
100001 9 C 3rd line for 47050

So the lines with a line_type 'C' are a continuation line for the description. The problem is they are just numbered sequencially and there could be any number of them. I want to end up with a results set something like this:

order_no order_line_no line_type product long_description
100001 1 P 47020 1st description for 47020 2nd line description for 47020
100001 3 P 47030 1st description for 47030 2nd line description for 47030
100001 5 P 47040 1st description for 47040 2nd line description for 47040
100001 7 P 47050 1st description for 47050 2nd line description for 47050 3rd line for 47050

Any help or pointers on where to start looking greatly apreciated.

Thanks, Alex

Lost User

Alex Lush wrote:

Any help or pointers on where to start looking greatly apreciated.

I'd do this using a console-app; reading from the source-table, iterating order_no's, fetching them completely (the C's), and write the result to a destination-table. You could also omit the line_type column in the destionation-table; they'd all be 'P', making it a redundant statement. Whoever designed the structure should be introduced to "Normalization".

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

Lost User

You didn't mention what database it is. For SQL Server, the query might be something like this

SELECT order_no, order_line_no, line_type, product, long_description + ' ' + (SELECT B.long_description FROM Table1 B WHERE B.line_type = 'C' AND A.order_no = B.order_no AND A.product = B.product) AS long_description
FROM Table1 A
WHERE line_type = 'P'

Alex Lush

Apologies. It is for SQL Server 2008 R2. Good guess!! Will give your suggestion a go. Thanks.

mmccarth

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