|
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
|
|
|
|
|
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".
|
|
|
|
|
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'
|
|
|
|
|
Apologies. It is for SQL Server 2008 R2. Good guess!! Will give your suggestion a go. Thanks.
|
|
|
|
|
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
|
|
|
|